Visualizing your Stock Investment Account using Power BI with PostgreSQL Datasets

This article was written after this one that I showed how to securely connect to your investment account using Python and storing data in PostgreSQL. In this article I will show you how to visualize PostgreSQL data using Microsoft Power BI.

After creating a beautiful first visualization I will write a new article to use Python to get a financial measure used by most investors: Moving Average.

After that, I will show how to create a Predictive Algorithm to find some data pattern and track behaviors behind the data.

I put a copy 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 second journey.

Architect the Visualization

Now that we have the basic stock data stored in PostgreSQL, we can start creating our Visualization. I will develop a Power BI Desktop Report. It is free and you can always collaborate with your team if you have access to Power BI Service on Azure.

One of the most used charts to show Stock Data is Candlestick. With Candlestick you can easily see the ticker (or Stock) basics metrics: open, close, high, and low values for each day. And you can see a tendency line as well. Below you can see the final version of my Power BI report:

It is quite easy to understand Candlestick chart. If you are not familiar how to interpret and use it, I  would suggest a reading on this. The little bars for each day show the Stock Open and Close values. When it is Red, it shows the Stock went down and it turns green when it went up. The vertical lines that crosses each small bar represents the highest and lowest values of the day. The green and red lines crossing all chart are the Moving Averages. I will show how to add these two metrics in my next article.

When I installed Power BI Desktop, there was no Candlestick visualization available. One amazing thing in Power BI is that you can easily find and install free visualizations. The community around Power BI is highly active and there are visualizations for basically everything you need. It is easy to install and use. No need for advanced customization or IT knowledge to make it work. I suggest you download a free Candlestick visualization from OkViz available here. It worked well to me.

Creating a Story

My experience tells it would be especially useful follow Scrum methodology and create a story for each Report or Dashboard you develop. In this case, because it is a single analysis, let’s make it simple.

You can say that, as an investor, you want to see a Stock performance over a specific period with its main metrics to support your investment decision. You will consider this report done when you are able to filter a Stock, start and finish date and see its main metrics in a Candlestick chart.

With this story and acceptance criteria, it is possible to create a Report using some metrics we collected in the first article (Symbol, Date, Open, Close, High, and Low). Because it will support your investment decision, you will filter just one Stock and specify a date range. Based on this date range you will see the Stock performance.

Now we have a plan with a clear scope and expectation to create Power BI Report. Let’s get started!

Accessing Stored Data

I am assuming you installed Microsoft Power BI and we are ready to go. And you have Stock data stored in PostgreSQL like I showed in this article.

Power BI Desktop initial page should be like this:

First let’s import the OkViz Candlestick visualization. Click on the three dots below the standard Power BI visualizations and select “Import a visual from a file”. After a warning message regarding security (you can ask not to show it again just clicking the specific box), select the file you downloaded (in my case it is called candlestickByOKViz.1.0.4.0, but you can have a different version). You will see a message like this:

If you do not get this message, review the file you downloaded and make sure you selected the folder where you downloaded it.

Now you have the OkViz Candlestick available below the standard Power BI visualizations.

Now let’s get the data from PostgreSQL. In Home Menu, click on Get Data button in the upper left position. Start writing PostgreSQL (like I did in the following figure) and you will see the right panel PostgreSQL database. It should be like this:

Click on Connect. On the next window, fill out the information regarding where PostgreSQL Server and Database. I selected DirectQuery because I do not want only to import data. Because I will get more data each day, I prefer using this option.

Select StockMarket table. You will see rows and columns in the right panel. Power BI has strong Transformation capabilities. No need to any transformation. Just click on Load.

You will see StockMarket table and column list on the right panel (Fields).

Click in any part of the white canvas then click on Candlestick chart under Visualizations. Your canvas should be like this.

Below the Visualizations option you can see all chart fields (Axis, Open, Close, etc.) you will link with StockMarket table. Drag datetime column under StockMarket table and drop in Axis. Drag opn into Open, Last into Close, hi into High, Lo into Low, and Last into Trend Line. Resize the chart to see better the results. At this time, your canvas should be like this:

Move Candlestick chart to the right releasing some space for the slicers (or filters). Click in a white part of the canvas outside Candlestick chart. Now click on the Slicer visualization.

This is a quite simple way to filter (or Slice) your data to show only what you want. If you have more than one chart, it will apply to all of them simultaneously. Resize it to fit on left of Candlestick chart. Let’s slice our Candlestick by Stock. Below the Visualizations you can see Field and below Add data fields here. Drag Symbol from StockMarket table to this field and you will see all Stocks with a check box on their left side. Click on any Stock and see that the Candlestick is now closer to what we want to show.

Because we want our user to select just one Stock each time, let’s change Slicer some behaviors. The first one is use of Dropdown instead of List. Click on the down arrow on the right side of the Slicer and select Dropdown.

Now click on the Format (whose icon is like a brush) below the Visualizations and then click on Selection controls. Change the options to fit a Single select like this:

Now let’s create another Slicer to select the date range. Click in any white part of the canvas and click on Slicer visualization. Drag datetime from StockMarket table to the Field under the Visualizations panel and you will see that there is a date range. Now you can select the date range easily.

Even with a lot of data to show, now it is possible to slice just the data you want to see. Try different formats. I like using Relative Date where you can select, for example, last 30 or 60 days. It works well for Stock analysis. You can reach this option in the same way you selected the Dropdown for Stocks (previous step).

Now let’s add some basic metrics for the selected Stock. Click in any part white of the canvas and then click in Card under Visualization option.

In Fields and Add data fields here, drag and drop Last from StockMarket table. Click on the arrow in the upper right of the field and you will see a series of available aggregations, like Sum (default), Average, Minimum, etc. Let’s create one Card for Minimum, Maximum, Average, and Standard Deviation. For each one you will need to add a new Card and repeat the process.

One important Power BI feature is the Alignment. It works like in PowerPoint. Easy and intuitive. You can distribute horizontally all Cards. First put one Card in each side and the others anywhere in the middle. Then in the Format menu, select Align and then Distribute horizontally.

Now it is time to make some enhancements, like applying a background and create a title. Click anywhere in the white canvas and you will see in Visualizations panel, under the Format, Page background. Click on it and select a color that seems good to you. In my case I will select the White 10% darker and 60% Transparency. This will make a good contrast with the white background from charts.

Now I will add a blue rectangle to put Report Title. Go to Insert menu and then select Rectangle under Shapes.

With the rectangle selected, use the Format Shape to customize Line, Background, and any other option you like. I set Fill off and add a blue Background. I set Line with the same Background color since I could not set it off.

Now let’s add a Text Box. Still in Insert menu, select Text Box. Position it in the middle of the rectangle and set a title to your report, like Stock Market Analysis. The nice think Microsoft does is to help you position exactly in the middle of your canvas. Look at the dotted lines and you will get it. Now use the Visualizations panel to set the same rectangle background color and change the color font line. I prefer to set the font color as a bold white. It does not seem too intuitive to me, but you can change the font color using the little down arrow on the right of font size (select the text to see it).

At this time, your Report should seem like this:

In the next article I will show you how to create a Moving Average based on the stored data in PostgreSQL with Python. I will use Power BI dataset to link both Datasets in this report.

Hope you enjoyed!

See you next time.