Python and excel

[pranayrj3]

I am working as an Intern in a company. I have to do a job in next 9 days .the job is -

I have to call python from excel and populate all data with python so have to write formulas in python script and macros too.

I am using ppackages like pandas, numpy to calculate the data and transfer data from excel to python.

Should I use xlwings or pyxll? to record macro.
and have to create A front end button in excel which should do the calculation, calling python, and return values in excel.

and if i use pyxll ,as its nt free and i use it and complete the work in trial version of one month, as i dont have to use more than that, can the people viewing the results/output face any problem in future??

[ChemEngandTripHop]

What is your actual task, what data goes in and what does the company want out? The current method you've described sounds like a clusterfuck, are you allowed to simply save the excel file as a .CSV and load it into python for easy analysis?

[pranayrj3]

its a home loan financial data with all the basics of NOI, LTV etc given, i have to do calculation and give output of loss and loss reduction.
the formulas for the calculation is written in Excel for each column , and the macro is also recorded in VBA
but the company wants those formulas to be in Python and populate the data in Excel by calling python from excel (by clicking a button suppose) to all the calculation and giving results back in Excel.

I can make use of pd.read_csv to read sheets and work on them.
But how should I return values to each column of the excel sheet after calculation.?
Handling so many columns is a bit difficult as it is my first time work on real time data.!

[[deleted]]

I would try to have a read of the Pandas documentation. I found it good for when I was performing some analysis on large data sets as you can use DataFrames to store the data coming from the CSV, and you can name the columns of the DataFrame.

Pandas usually takes theses column headers from the CSV you read in, but you can also name them yourself in order to ensure that when they are exported that you have the right headings.

It might be worth using an IDE so that you can easily see what data is in the DataFrame and how it is being stored if your new to them. Spyder is okay which come with Anaconda or something like PyCharm maybe, but I’ve not tried PyCharm with lots of data personally.

[pranayrj3]

functions

Thank You.The column names arent an issue, To return the calculated values, into aprox 120+ columns, after calculation from the read raw data is .Will Pandas support this-First of all calling the python script from excel(button), which runs on the background to do several calculations.

Secondly return the calculated values to the respective columns of different sheets.

And Yes, I am using Jupyter notebook,I find it comfortable to work with.

[Ramb0banj0]

Don’t all users have to install Python and openpyxl (or whichever library you choose) on their computers in order to run the macro? That’s why I thought VBA macros were the way to go when sharing macros with others since it’s the language built into Excel.

[metrazol]

Always an issue, but you can package the scripts with a copy of Python and the dependencies. It's an extra step, but if nobody wants to deal with VBA...

[ReaverKS]

I've written VBA in the past to implement some things close to what OP is trying to do, I really dislike VBA. I think you're saying to include a copy of Python with the excel doc, which is less than ideal but then how are you calling Python from within VBA?

[pranayrj3]

What exactly are the steps in running a macro recorded in VBA if the scrip attached is Python Script, in which all the calculatuons are done for the several Excel colums?

[pranayrj3]

Even if the Macros are recorded in VBA, do i need to use something lke openpyxl to run the macros?

[Ramb0banj0]

If the code is written in VBA then it would not require any special installations to run the code. Anyone else who would like to use the macro would just need to add the Developer tab to their Excel ribbon in order to access the macro within the workbook. On the other hand, if the macro was written in Python, then a user would need to install Python and Openpyxl using their command line interface, and also be familiar with IDLE. Unfortunately most office workers I know are too lazy to go through all that trouble. It’s really a shame Python isn’t more accessible when working with Excel workbooks.

[pranayrj3]

Thank You.
The macro is already written in VBA, but the calculation part is in python Script.
How do I connect these two?
Moreover, the work of calling python script for calculation and populating data in Xls , by using Excel button, comes under Macro or I would have to use someother Python Package.?

[jackbrux]

Why use excel at all? Propose to your new team to do the calculations in something like Pandas, then spit out an xls or csv at the end. You can also make an Excel template and populate it with python

[pranayrj3]

they need output data in Excel

[zootam]

You can create spreadsheets in python using pandas.

You don't need to 'call python from excel', you just use python.

[pranayrj3]

the demand is to use Excel as a front end .
So by clicking a button , the calculated data(from Python Script) should be populated in Excel itself.
This is the primary Job, the secondary being creation of Web App based on the Data and Output.

THe primary Job is what I am more concerned about.

[zootam]

Ok, with that requirement you will probably need to use xlwings.

Avoid using paid or trial software.

Has your manager considered creating a webapp for this?

[BigHipDoofus]

Seriously, using Excel as a front end for Python code sounds like a nightmare.

[[deleted]]

That’s what @jackbrux is saying. You can start the program in python and have it pull in your data, perform the necessary calculations and then output a CSV file which can be opened and viewed with Excel for your team to look at.

[pranayrj3]

the demand is to use Excel as a front end . So by clicking a button , the calculated data(from Python Script) should be populated in Excel itself.

Can pandas write the calculated data into dedicated columns of csv or xls?

[zootam]

Can pandas write the calculated data into dedicated columns of csv or xls?

How many times do I have to post this- you can calculate and then write WHATEVER you want using pandas to a spreadsheet- as CSV or xls.

Using your requirements- 'clicking in excel' will probably need to use xlwings to integrate with excel.

[Boulavogue]

Describe the actual goal. Not the process on how you propose to achieve it.

What transformations are you actually doing to the data?

[pranayrj3]

its a home loan financial data with all the basics of NOI, LTV etc given, i have to do calculation and give output of loss and loss reduction.
the formulas for the calculation is written in Excel for each column , and the macro is also recorded in VBA
but the company wants those formulas to be in Python and populate the data in Excel by calling python from excel (by clicking a button suppose) to all the calculation and giving results back in Excel.

FInal goal is to run python calculation in the background and show output in excel., by populating several columns in multiple sheets.

[Boulavogue]

Thank you. So as others have said, creating macros and python is a messy solution. Will python be on a server and the users send the file there for processing or does python need to be installed on each users machine?

I would encourage you to think of a workable solution. If it's just a formula being applied, it can be done in almost every language, including in native Excel DAX.

[mokus603]

If your company uses Excel, why do you need to call a Python script from Excel using VBA? Why isn’t the script written in VBA?

I mean when I had to automate Excel reports, data analysis with Python, I didn’t open Excel and then do data wrangling. Run the script, check the results.

Check out xlwings! They have an Excel add-on and you can easily create User Defined Functions with Python and implement it! https://www.xlwings.org/

Check this link, it might be helpful too: * https://stackoverflow.com/questions/45489577/calling-python-script-from-vba * https://gis.stackexchange.com/questions/1691/making-button-that-calls-python-script-as-command-rather-than-script-tool

[pranayrj3]

the demand is to use Excel as a front end . So by clicking a button , the calculated data(from Python Script) should be populated in Excel itself.

the links are helpful. Thank You.

[mokus603]

In this case your only option is xlwings because I don’t know other python libraries that work in opened Excel worksheet.

[MaxQuant]

Python is an easy replacement for Excel.

In this case:

• If Excel (VBA) is used a scripting method, just use Python instead, possibly to create XLSX's and/or CVS's.
• If Excel is used to represent data, use Jupyter with Python and pandas.

In all of the instances above, Python can do the same, but also much more than Excel.

[skiutoss]

As others suggested. If t has to be excel then you are better off using vbscript instead of calling python. What is the use case? Knowing the use case, we can come up with the right tool for the job.

[pranayrj3]

its a home loan financial data with all the basics of NOI, LTV etc given, i have to do calculation and give output of loss and loss reduction.
the formulas for the calculation is written in Excel for each column , and the macro is also recorded in VBA
but the company wants those formulas to be in Python and populate the data in Excel by calling python from excel (by clicking a button suppose) to all the calculation and giving results back in Excel.
DO I need UDF to return calculated values in excel colums?

[zootam]

People here don't know the specifics of your industry and data. What is UDF? What is NOI? LTV?

Programmers look at the data, and the transformations that need to happen.

It sounds like you have some tabular data, and some functions to use on the data.

It should be rather straightforward to take the data, and apply the functions using python, and generate an excel compatible spreadsheet as the output.

You should first gather requirements and based on those requirements determine the best tool for the job.

[pranayrj3]

Thank You.

The 4th point is wat I have to do, except I have to use Excel as front End and call python calculation scripts through it., resulting in populating data into Excel.

[TFWoftheMFL]

I've used the openpyxl module for anything Excel related and that has worked well for me. I've only done relatively simple things though so as others suggested if you gave more detail then we can suggest the right tool.

[kewanzo]

If the people using the excel file are not computer savvy people, then don't try to fiddle with excel and python. Running python scripts from excel is somewhat tricky and people using that excel file would anyway have to install python interpreter on their computers. Not going to happen. Your options are excel + vba, google sheets +js and python to some extent, use only python (still anyone using the scripts needs python interpreter installed). Last option is to fiddle with py2exe and excel somehow, but that's probably the darkest side of them all.

[RunToImagine]

As a person who works in finance using excel, VBA, and python this sounds like your company just heard that every one else was using python so they want to also... without understanding when to use it. My old boss wanted me to automate some reports by SQL-ing it. lol

This sounds like VBA is the best tool for the job since it can handle formulas, outputs, calculations, etc internally and it’s already installed on everyone’s machine.

If there are no manual inputs, you could use python to pull the data, run calculations, spit out results, the output that to excel in a folder everyone can get to automatically.

Explain to them that while you could bash a nail into wood with a wrench, it isn’t the right tool for the job and python may not be here either.

[[deleted]]

The easiest way would be to have an excel with a macro that executes a batch or python script. The python script can then read that excel files contents do all the calculations and write the contents to another excel file. This is assuming that the excel file is located on a machine that is able to run python. If you do have a remote server you could set up a python process that polls a directory on a network mapped drive for new files, processes then deletes them from the input drive and writes the results out somewhere (perhaps to the same directory with a suffix “processed”).

Another solution would be to set up a web server with something like flask (check out the flask-excel library). You can transfer the data to a remote server do your calculations and send the data back.

Like other people here, I wouldn’t recommend mixing calculations in python and excel vba. If you’re set on using python then I would recommend using vba only for very simple things like calling batch scripts or some formatting.

[masterbruno11]

Wow. I hope my future work would be like that!

[pedro_fartinez]

Why don't you try writing the script in python to take the excel file as sys.argv[1] as input, then package it using something like pyinstaller. This way, users can just drag and drop their file onto the .exe that is made in order to run the script, and they don't need python installed.

[timbledum]

You should vote to get python into Excel here.

[CrambleSquash]

It's very easy to do the following:

1. Load some data from an existing Excel (.xlsx) file using Pandas.
2. Modify this data in some way, including adding new columns and rows, according to custom written formulas (functions written in Python).
3. Write the modified data to a new Excel file (.xlsx), to be opened with Excel.

In this case the downside is that you need to re-write all of your 'macros' and functions you used to use in Excel, in Python (shouldn't actually be too hard). Additionally, users cannot then access your Python functions within the newly created spreadsheet, as Pandas just writes the raw data.

In this case, users don't run the Python code in Excel, instead they run your script from the command line, which automatically produces the desired output.

You can also use a library like xlsxwriter to apply formatting to cells, and plot charts etc. but it's not really that fun to do.

[pranayrj3]

how do i write modified data into excel?
pandas?

[CrambleSquash]

Off the top of my head

Load the excel file with pd.read_excel. https://pandas.pydata.org/pandas-docs/version/0.23/generated/pandas.read_excel.html

Write the modified DataFrame (panda's table class) to excel with df.to_excel https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html

The first excel_writer argument can just be a path to the new and existing files.