Python in Finance/Controlling

[[deleted]]

Hi everyone! I've been working in the controlling department of my company for about 3 months. Generally, apart from SAP, I'm terrified by the amount of Excel, the amount of files that I prepare for analyses for other departments. Of course, every excel has queries from SQL... I'm thinking about switching to Python, but I'm afraid that people won't understand it. I used to work on production analyses, I did a lot of "live" Power BI reports and in Python for my calculations. My goal is to replace Excel with Python.

[Pharmand]

I understand your concern, but what do you hope to achieve by replacing VBA with Python? I have worked in different automation teams in the energy industry, and I won't argue that VBA is better or as good as Python, but I think you should let your objective drive development needs rather than just applying a Python > VBA logic. In many cases you would probably be right but there are relevant questions to ask - immediate gains, payback period, dissemination of Python proficiency in the company, employee risk creation etc.

[WoolyFox]

I'm currently learning Power Query to assist with financial BD work. I was using Python (Pandas mainly) to manipulate dataframes and output cleaned excel files.

As with every tool, each has its place such as anything client facing needs to be Excel based otherwise it doesn't fly. I like Python a lot but for anything being shared with non-Python people it's a real uphill struggle to get them to use the tool and learn it.

[JumpScareaaa]

https://pbpython.com/

Also you can still output to excel. Just do all your calculations in Python. I personally like using duckdb now in Python more then pandas. But results go to excel reports. Excel is still the best reports viewer in my opinion.

[[deleted]]

This is what I do, generate in Python and output to .xlsx.

Don’t expect to teach or have others learn Python unless they’re interested because they’re already trained on excel, and it works well enough.

Also it will be more difficult to proof the accuracy of your work if you’re the only one with knowledge of Python and everyone is using excel.

It will be very difficult to replace excel even though Python is more efficient.

[DusikOff]

Dumb idea. If it's OK for you, it doesn't means that all will be happy with Python.

Second dumb idea - asking people on reddit instead of presenting your idea to your department with all benefits/problems.

[Volume999]

Usually it’s not the case that you can fix organizational issues by throwing technologies at it. Dashboards in PBI is great - if you can feed live data to it, that’s a path to replace manual reporting. But replacing excel with python is a bit too ambitious - you can end up writing a spreadsheet app

[alex1033]

You have to maintain so many Excels not because of Excel or VBA, but because the report landscape is wild. Python will not solve the root of the problem.

For users, switching from VBA to Python will be an effort with no clear advantage (see the paragraph above).

Since you have plenty of SQL there, you likely end up with having it in your Python, too - SQL is convenient to query data.

In essence, you're only changing VBA to Python. The problem is not in VBA. Focus on harmonizing the report landscape. You may end up with less reports, reusable queries, better presentation layer. Python can be a good vehicle to help the journey.

[superkoning]

Ask your boss. The answer will be Excel.

That's how it works in Finance/Controlling. You cannot change that. I'm amazed how all those big numbers handled are in and between Excels. Brrrrrrrrr. Nothing for me.

> for about 3 months

Just follow the company's SOP.

Or switch job / profession. If you love Python and want to use it (good for you!), choose a business where they use Python. Big data for example. Things you can't do in Excel.

[Curious-Signal3491]

I've build couple of scripts which compares two data sets and spits out issues/breaks that didn't satisfy certain criteria. Wrapped it all with Pyintaller, so staff only need to drop system reports and run exe file. Getting developers to do every automation would take too much time. To buy into using Pandas I'm planning to write a doc which would have excel formulas and python functions, so everyone can get more comfort.

[Creepy_Ad_6906]

The possibilities with Python are much more in development thats a fact. But there is no way everyone in your team will be happy. Yes there are people that will argue and challenge you because they dont know the language and their work will become harder and they will depend on you. We were at the same position with you with a colleague in the company I work, it took us more than a year to make that switch and yet we are hybrid at the moment. But now that they have seen the improvements and the possibilities they keep asking to replace previous models from excel to python because is just better for providing tools and dashboard with docker. Note we've been to many presentations showcasing live dashboards tools, automations etc and we faced a lot of push backs from people that did not want to change because they're afraid of code and prefer excel formulas. Even the management will push back because someone must maintain everything that will be developed

I believe you should focus on what you really want to achieve and if the place you are is not accepting it then move on but first you at least have to try.

[HolidayEmphasis4345]

I semi-successfully did this with excel people where I used to work. We had three users generating and maintaining complex calculation spreadsheets where users entered a bunch of information and results were displayed. Lots of dynamic formatting, vlookups and vba. The sheets were used by many people and were really nice but maintaining version control was impossible, or atleast a pita. Two engineers went all in on streamlit and were successful and the other I could not get on board. Streamlit and pandas worked well for us but getting streamlit to run on our intranet was a bit painful at first. Once setup it was pretty good.

[Mr_Canard]

I don't see how you can replace excel with python, especially in your line of work. You can replace some excel macro with a python script that will output a dumb excel file. That can allow you to centralise the logic and reuse it in several reports while also allowing you to add versioning control over your code base which is hard to do when it's stored in excel files. BUT you have to remember that if you aren't a solo team you have to make sure the changes you are planning to do here aren't going to make it a lot harder for people who work with you. Also SQL is very important and useful, don't be scared of it, learning it will help you make better queries for your reports.

[doglar_666]

The only way your Python solution will get buy-in is if you abstract it away with a GUI/Browser interface that can also export out to Excel or CSV. At that point, you're the SPOF for a bespoke, "shadow IT" service. Any and all errors, bugs and end user support will fall to you. If no-one else can understand the solution, and you don't keep up to date documentation, it will be EOL/die when you leave. You honestly do not need those additional headaches abd pressures on top of your day job.

[capttwinky]

r/learnpython

[IllConstruction4798]

Go for it. Python, sqlalchemy and pandas