Adding Python code to our Investment Account Power BI Visualization

This is the third article of a series where I first showed how to connect to your investment account using Python and saving stock data in PostgreSQL and, in the second one, I showed how to create a visualization in Power BI using that data. Now let’s add Python code to our Power BI visualization to get two important moving average metrics to analyze our stocks.

I put a new version of my StockAnalysis Power BI Report in my Git account (https://github.com/CPoderoso). So, fell free to use and improve it.

With that being said, let’s start our third journey.

Architect the Visualization

Our goal is to create a visualization for our collected stock data. Following the steps presented in this article, you will have a beautiful Power BI visualization like this.

Integrating Python and Power BI

It is easy to add Python code in Power BI. There are two ways to do this. Let’s first add a new page to our visualization to test the first feature. Click on plus sign (+) beside Page 1. Now click on Py on Visualization panel and you will see a screen like this:

Click the Enable button. Now let’s add some fields to this visualization. Select datetime, last, and symbol columns from public stockmarket table. You see that the Python script editor automatically created some rows in the box with Python dataset definition. Let’s add a very simple code just to see how it works.

import matplotlib.pyplot as plt 

dataset.plot(kind='line', x='datetime', y='last', color='red')

plt.show()

Click the Run icon on the right of Python script editor and your screen should be like this:

It is showing all data summarized what makes no sense for any kind of analysis. Let’s filter just one stock symbol and see if it is better. Click any white part outside of Python visualization and then click the Slicer that is under Visualization panel. With the Slicer visualization selected, drag and drop symbol from Fields panel to Field under Visualizations panel. You can simply click the box beside symbol and it will take the same action. Filter one symbol and you will take a screen like this:

Ok, now it makes more sense, but I see no big advantage in doing this in Power BI. Power BI is much better and can do more beautiful visualizations than Matplotlib. But naturally you can use this feature to make more complex calculations and show something that goes beyond this.

The second way to integrate Python, in my opinion, is much better. It will use Python to execute some code and populate a kind of table in Power BI. This code can run on the fly and get streaming information coming to your data model (or Data Lake) to, for example, return a Predictive Metric. It opens a lot of possibilities to feed your business users with accurate, clean, and predictive data. Then they can rely on these data to take better decisions.

Click on Model icon below the Report button on the left side of your screen. Then click on Get data and write Python in the box as I did below.

Click on Connect. This will open a Python script box where you can write your code that will return a Dataset to Power BI. This Dataset will work as a table. You can join and mix data with other tables to compound a visualization, create new columns and so on.

It is recommended that you test your code using an IDE to speed up your development. One important note is regarding your Python installation path. Check if it is correctly set. If it is not, update Python installation path on Power BI Options > Settings.

In my case I used MS Visual Studio to test my datasets. Here is the code (there is a copy of this in my Github).

I will briefly explain what I did. First, I imported matplotlib, pandas, and psycopg2. The first two were used to calculate the Moving Average (rolling(10), rolling(50) and mean) and the last one to connect to PostgreSQL.

You can see that I applied a simple SQL query to get a kind of Primary Key (PK). It was created by joining Symbol and Date. Then I add symbol, date, and the stock last value database columns. You can change the last value by any other one that is useful to your analysis. You should be asking why I created that ugly PK. You will see in a moment.

Then I created a Pandas Dataframe. Based on this Dataframe I created two moving averages for 10 and 50 days. Again, you can change them to any number of days. I created another Dataframe (df_agg) just to show how to put everything in just one structure. You do not need to create these 3 Dataframes because they have the same data. You should create two (one for each period you want to analyze) or just one and have both moving averages together. It is kind of tricky to get just one Dataframe with two aggregations, but it worked well.

Now let’s put this code to work in Power BI. Simply copy and paste this code the Python script screen. Your screen should be like this:

In the next screen, select only df_10 and df_50 Dataframes and click Load. You can see that these new Dataframes seem exactly like a table for Power BI.

If you are familiar with Relational Data Modeling you know that we will need to link these tables with a common column. Otherwise they will duplicate data and the aggregations will be wrong. Note that there is a link between these new Python tables, but there is none for the public stockmarket table. That is why I created that ugly PK in Python. We will need to create the same ugly new column for our public stockmarket table.

Note that, because now we have a Data Model, Power BI shows a new icon between the Report and Model left menu. This new option is Data. Click there and it will open a screen to edit tables. Select public stockmarket and click the New Column under Home Top Menu. You can click the three dots on the right of the table name and select New Column option.

When you insert a new column, you unlock a great Power BI feature. You can add any DAX (Data Analysis Expressions) function. DAX is a powerful tool to use that extends Power BI functionality. You can get more information about DAX here.

In our case we just need to replicate the same (or almost the same) command we use on the SELECT. Just type like this:

Now return to Report clicking the first button above Table. You will see that there is a new column named PK in public stockmarket table. Drag and drop this new column in the column with the same name into df_10 table. You will see this screen:

This screen shows you the new relationship that is being created. It has a 1:1 cardinality – what means each row in one table has only one reference in the other. Power BI took care of everything else. Just click Ok and now the tables are linked. Like this:

Select df_10 table and let’s rename Close column. Right click Close column and select Rename and type MA10. Repeat the process to df_50 and rename to MA50. If you do not do this, Power BI will try to use both values together when plot the Candlestick. Trust me, it will not be a good idea.

Let’s add these two metrics in our report. Click the Report button above Table in the left menu, select our beautiful Candlestick visualization and add both metrics. Note that both new metrics must be under Trend Lines option. Your screen looks like this:

It is nice, but you can change the MA10 and MA50 colors to better demonstrate the difference between them. Do as follows:

Ok. Now we have finished our beautiful Power BI visualization that reads PostgreSQL data and get two Moving Averages from Python.

In the next article I will use this data to create a Predictive Metric using Python and, again, put it into Power BI.

Hope you enjoyed this article!

See you next time.