Can You Do Financial Planning and Analysis With Python?
The short answer is yes! You will discover how in this article.
The Problem
If you work in the Financial Planning and Analysis area, chances are you are dealing with large Excel models on a daily basis.
Most probably, the Excel file has grown significantly and you are experiencing the typical problems of: unstable file, lack of performance, versioning control issues, linkage governance (when someone inserts a row or a column, and the linked files are closed), among many others.
There are other types of problems of working with large spreadsheets that are not so obvious to identify:
- Transparency: you are the only one who understands your spreadsheet and it is hard to communicate how it really works (users need to trust you)
- Multi-Dimensional calculations: Microsoft smelled the need for that when they implemented matrix formulas but, to be honest, it is an extremely rigid and difficult way of working in Excel
If the problems above sound familiar to you, that means you have reached the limits of Excel and should consider moving to another platform.
Spreadsheets were developed as a digital replacement of accounting tasks. Excel was launched in 1985 and the basic paradigm has not changed since its invention. Think how the world changed since 1985. Don’t you think it is high time we update the way we do financial planning?
The question is where to take the next step in Financial Planning. Which environment offers the ease of use of Excel also solves the problems Excel has?
The short answer is Python, and I will explain to you why.
Smoothing the transition from Excel to Python
I want to make your first contact with Python as simple and pleasant as possible. If you don’t feel comfortable installing apps on your computer, you can work online here.
If you are already committed to give Python a try we will start installing Python 3.7 version first (Notice it is not the last one!). You can download it from here after selecting the appropriate version for your operating system.
Then we will install a Python application intended for Business Planning & Analysis called Pyplan, following the instructions below. Further installing alternatives (Mac/Linux/Anaconda) can be found here.
Press Windows+R to open the “Run” box. Type cmd and then click OK. Then run these commands:
# Install
python3.7 -m venv pyplan
pyplan\Scripts\activate.bat
pip install — upgrade pip
pip install pyplan-ide
# Run
pyplan\Scripts\activate.bat
pyplanAssuming everything went right on installing, your default browser on your device will open showing the Pyplan welcome page:
Notice that the address bar in your browser will show http://localhost:9740/# this indicates the Pyplan app is running on your own computer so that it does not require internet connection.
The second time you access Pyplan, you just need to open the console (Press Windows+R to open the “Run” box. Then Type cmd)
# Run
pyplan\Scripts\activate.bat
pyplanChanging some paradigms
Years of working in 2D spreadsheets have flattened our minds for thinking about calculations. Real world problems are multidimensional rather than two dimensional. For example, we deal with units sold by regions, products, sales channels, and time, and those are four dimensions.
If you have to calculate revenues as units sold times price that will require choosing two dimensions, probably time for columns and products for rows, and replicate the table as many times as the product of regions times sales channels.
Wouldn’t be nice just to write
Revenue = Units_sold * Priceand let the calculation engine automatically solve the alignment of dimensions?
Well, in Python the answer is a big yes and that is not the only good news!
But, before we get into the Python multidimensional world we need to reset our mind, and like Neo in the Matrix movie, start discovering dimensions and matrices in our planning model.
What are dimensions (aka indexes)? In short they are the list of rows and columns labels that define what a cell number represents. The matrices are the tables containing numbers grouped together as the figure below illustrates.
Making the first steps
Now we are ready. Click on “Create a new model” to start working on our model. Provide a name and click Ok. You will reach the workspace to start modeling as shown in the image below.
The first thing we will do is to read a csv file with historical sales data.
For that we will click on the wizard section located in the right toolbar and drag and drop a Read CSV node. It will prompt a file location, we will indicate the following url:
http://img.pyplan.org/1000_Sales_Records.csv
It will first show you a preview of the file. Click next and Pyplan will show you a suggestion of indexes found in the csv file. We don’t want any indexes yet so click “Unselect All” and press the Submit button.
Now we have created our first node, named “node1” by default. We will rename it as “Sales Database”. To do that click on the node to select it, then click on the Properties tab and type Sales Database on the Title field. After you hit enter, you will notice the Id of the node changes to sales_database. This is the Id we will use to call this node on the following calculation steps.
Now let’s go back to the Code tab and click on the run button.
Excel only has one type of object, the cell, that can take any type of values: numbers, text, dates, etc. In Python you can have different types of objects like a single value, a list, a database, a matrix or any other different type that must be considered when operating.
After evaluating the node Pyplan realizes which type of object the node is, in this case, it is a Pandas dataframe, and based on that displays a series of tools to assist working with that type of data object.
Pandas is one of the most used Python libraries, it is an in-memory database object type, with an extremely complete and powerful list of functions included. You can read more about Pandas here.
We will use the “Create DataArray” wizard to generate a multidimensional node with the units sold. Replicate in your wizard the selection shown below:
After clicking Ok you will see in the diagram the created indexes named Region, Item Type, Sales Channel, and a node named “Sum of Units Sold”.
Just double click on the node to evaluate it. A pivot table will be displayed. You can slice and dice the table and create a visualization of it as indicated in the figure below, clicking on the dots and selecting Table & Graph > to right.
Now we will create a second node as the Mean of executed prices opened by Region, Item Type and Sales Channel. To do that we have to select again the Sales Database node and at the Code tab click again on the Create DataArray tool. Now the wizard will open preselecting the existing indexes. Check the Mean of Unit Price and click Ok.
Check Mean of Unit Price to generate the matrix
Now we have the Sum of Units Sold and Mean of Unit Price nodes created we are ready to calculate Revenue as the product of the two previous nodes. Drag a new variable node. Name it Revenue then go to the Code </> tab. Reference the “Sum of Units Sold” by pressing and holding the Alt key (option key in Mac) and clicking on it. This puts its ID (name of the node) into the formula ( you could have written it if you knew it already). Type the multiply * then hold Alt and select the “Mean of Unit Price” node to complete the formula.
Great! we have realized our dream of being able to tell a modeling platform to compute the Revenue in plain english as Units Sold times Prices. You can run the node clicking on the yellow run button and inspect its console output. Much more interesting is to double click to evaluate it as a table and create a graph visualization as shown in the image above.
As a homework check what happens if you create a Price node just indexed by Item Type and create another Revenue node defined as the Units Sold time the new Price. Can you guess how the calculation was made?
In a coming article we will complete the model and construct a basic Profit and Loss Statement.
As any change in the way we work the first steps are the most difficult. As a former Excel heavy user I can guarantee it is worth the effort.
Give it a try and let me know your feedback!
