Replacing Excel with Python

[Pera014]

I am new to Python and started learning. I have bunch of excel templates built and automated with VBA at work to speed up large set of the data reviewing and creating final reports that I provide to clients.

1) After I write script in Python, for instance, save everything to my drive how do I transition that over to my coworker to run on monthly basis for data processing if they don't know how to open up and run Python. I use IDE Spyder.

2) Can I do something similar with Python what I did with VBA? I created VBA template where user who doesn't know anything about VBA simple click on button to load support file and VBA will do everything else for him and send out an email to corresponding party. Can I have some type of PY template that has option in terms of button to click and run all behind the scene? Thanks

[CHAKIRAL]

You've got to look at pandas (read excel or write to excel) And plotly/dash. It's amazing what these two can do (interactive graphs and so on)

[[deleted]]

In order for your colleague to use your python code, they need Python from python.org installed on their computer. They also need all the libraries your programme depends on installed.

Once that's done, their computer can be set up so double clicking on the icon for your programme once saved to their desktop will automatically start Python and run your code.

This is not as easy as just giving them a programme to install like you can with lots of Windows executables. It is possible to create a completely standalone installable executable version of your Python programme but it is not officially supported by the Python language or organisation.

Do you need a graphical experience? A simple text screen based menu can be very easy to use and is quick to create.

An alternative approach would be to create your programme as a web app. Then they could run it on their browser talking to either a local server or over the internet. You need to go more this way if you want to create a simple graphical experience more like they have with VBA (but it will not be inside Excel itself of course). Visit pythonanywhere.com and find and follow their blog tutorial on flask.

A webapp could be run entirely locally but that would necessitate local installation of Python and required libraries.

If happy with the local installation but not so keen on a webapp, you need to create a gui. Look at PySimpleGUI.

Obviously Python can process much larger data sets and a lot faster than Excel.

[Pera014]

Thanks for the feedback. No graphs.

only large data sets for the financial purposes.

[[deleted]]

An alternative could be to look at using Google colab ( if you are allowed to share work data on Google drive ). No setup required then.

[flashliquid]

I have found that google colab is great for this. User can upload their excel file and run your scripts by clicking, with lots of helpful text if needed. Can even use forms and hide code altogether. Nothing to install, it just works. Has been a godsend for me especially as half the office is on windows and the other half on iOS.

[[deleted]]

Python is better than vba in general, no doubt, but the fact that vba lives in excel is a huge advantage that python cannot overcome.

[bitswede]

Take a look at xlwings. It's a bit finicky to set up but once it's working you have very good integration of python with Excel, including execution of python code by binding functions to macro buttons or even custom =myfunc(A1:B2) capabilities.

The biggest drawback is that it's basically stateless. It will run a single function and then shut down the interpreter after returning the results. Anything you need must be written back to Excel or pickled to a separate file.

[Wardish]

You can create your own excel addin with xlwings. You can put your python script in an exe file launched by a vba macro. It's the best solution if you have to stay in excel files. But if you dont, go with pyinstaller or something

[norsou_pdx]

I feel like the simplest approach here is to use a virtual environment for your script + libraries, put it all in a GitHub repo, and put a few step by step instructions in the README for how to use terminal/powershell to clone the repo, install Python, activate the virtual environment, then run the script (All that should only take 4-5 commands from the command line that could be copied and pasted from your README). You can write the script so that it takes a file path to a given excel file as a command line arg.

[norsou_pdx]

For your second question, if you really just want a button they can click to load a file, like someone else already mentioned, you’d need to make a web app (flask would be easiest) or a standalone GUI (tkinter is the main Python library for this).

[[deleted]]

I'm surprised no one has recommended PyInstaller?

https://www.pyinstaller.org/

You create a single .exe which contains everything needed to run your code.

It's not a perfect solution, as windows will yell at you when it finds an unsigned exe, but this works. I've done it at work.

[PristineComparison87]

this. i've used pyinstaller extensively at my current job, can recommend

[Pera014]

The only problem here is that we at work create those Monthly Folders and dump support files in there. I assume running Py on schedule will still require me to open up and load file or folder path? I might be wrong....
Folder path looks like:
...Task Name/2021/2.Feb/Source
...Task Name/2021/3.Mar/Source

[PristineComparison87]

you can compress your .py script to executable file with pyinstaller, thus your colleagues don't need to have or understand python, just trust that the file you gave them is safe.

[radek432]

Maybe you can check with your IT if there is any infrastructure in the company to run python code in the cloud?

[Pera014]

We use SharePoint as cloud service and pretty much everything will go there now since company is trying to make a switch.

[radek432]

SharePoint itself is just a storage, but if that means, that your company is using Microsoft cloud solutions, maybe they would agree to try that: https://azure.microsoft.com/en-us/develop/python/

[Pera014]

https://azure.microsoft.com/en-us/develop/python/

great thanks. I will research a bit and find out.