{"id":869,"date":"2022-10-28T18:52:26","date_gmt":"2022-10-28T18:52:26","guid":{"rendered":"https:\/\/eodhd.com\/financial-academy\/?p=869"},"modified":"2025-02-05T12:43:14","modified_gmt":"2025-02-05T12:43:14","slug":"price-predictions-in-power-bi","status":"publish","type":"post","link":"https:\/\/eodhd.com\/financial-academy\/stocks-price-prediction-examples\/price-predictions-in-power-bi","title":{"rendered":"Stocks Price Predictions in Power BI"},"content":{"rendered":"\n

Power BI for financial and trading data analysis using financial trading data from EOD Historical Data (EODHD APIs) data API<\/p>\n\n\n\n

If you want to learn how to install the EODHD Power Bi Connector for Financial Data APIs and activate your API key, we recommend to start with exploring of our Documentation for it<\/a>.<\/p>\n\n\n\n

Welcome back to the next instalment of my Financial APIs Academy<\/a> series for EODHD APIs<\/a>. The article can also be found here<\/a>.<\/p>\n\n\n\n

I\u2019ve noticed Microsoft Power BI<\/a> is becoming increasingly popular. At least many of the clients I work with now are using it. I wanted to investigate what it is, how it\u2019s used, and if it could be used for trading data analysis (which is an interest of mine). The Power BI Desktop<\/a> version is surprisingly free!<\/p>\n\n\n\n

The first \u201cstumbling block<\/strong>\u201d I faced is I\u2019m a Mac user, and there isn\u2019t a version for OSX currently. The forums are packed with requests for it, so hopefully Microsoft will release it at some point. There are however work arounds.<\/p>\n\n\n\n

I got it working using Oracle VM Virtualbox<\/a> (free) and a development virtual machine of Microsoft Windows 11<\/a> (also free). It works but it is seriously resource hungry. I assigned 4 CPU\u2019s and 16384 MB memory and it still did not feel desktop-worthy. I\u2019m not complaining, at least it works. I wrote on article covering how I set it up.
https:\/\/trading-data-analysis.pro\/power-bi-for-mac-88228621852e<\/a><\/p>\n\n\n\n

I managed to use a Windows desktop to capture some of the screenshots for this article, as the laggy feel to working within the virtual machine was working my last nerve \ud83d\ude09<\/p>\n\n\n\n

Power BI for Trading and Stocks Price Predictions<\/h2>\n\n\n\n

I have a keen interest in trading data analysis using a variety of methods. I\u2019m always on the lookout to evaluate new tools, concepts, and strategies. I was not sure if Power BI would be useful or not for this but I thought I would try it out and share my evaluation.<\/p>\n\n\n\n

The first step is we need some trading data to work with. I have a subscription with EODHD APIs<\/a>, and this is my main source of financial data. There are free tier options as well which are useful when writing articles like this. I found two ways to retrieve data from them directly into Power BI. Both are easy enough but have their own pro\u2019s and con\u2019s. You can decide which method you prefer.<\/p>\n\n\n\n

Option 1: EODHD APIs Power BI Add-on<\/strong><\/p>\n\n\n\n

I found a Power BI add-on on their website. It works well once it\u2019s installed but takes some time to set-up. I like that they have simplified the data retrieval and not having to work with REST API calls. The downside is that it requires you to download the add-on from Google Drive<\/a> and that you need to turn off add-on security in Power BI in order to use it. It\u2019s a pity that Power BI does not allow you to selectively trust add-ons rather than having to blankety turn them all of completely. I will however show you how it works.<\/p>\n\n\n\n

You will need to download the Microsoft Power BI Desktop add-on<\/a> from Google Drive, unzip the archive, and put it in your user Documents directory on your desktop\/VM.<\/p>\n\n\n\n

Directory Structure:<\/strong>
Documents > Microsoft Power BI Desktop > Custom connectors > EOD Power BI Connector.mez<\/p>\n\n\n\n

Once you have done that open Power BI and go to \u201cOptions and settings<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

This is the part I\u2019m not so thrilled about \u2014 under the \u201cSecurity<\/strong>\u201d global settings, scroll down to \u201cData Extensions<\/strong>\u201d. Make sure the radio button is on \u201c(Not Recommended) Allow any extension to load without validation or warning<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

Once you have done that click \u201cOK<\/strong>\u201d. You may be asked to restart Power BI which you will want to do.<\/p>\n\n\n\n

Click on the \u201cGet data<\/strong>\u201d menu option in Power BI. You will get a variety of useful options, but for this case we will select \u201cMore\u2026<\/strong>\u201d
\"Power<\/p>\n\n\n\n

If all has gone to plan and the custom connector is in the correct place, and the security has been relaxed, if you search for \u201ceod<\/strong>\u201d you should see the three options below. For this tutorial, please select \u201cEOD End of day data (Beta)<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

It will ask if you want to connect using a third-party service, and in this case we do. Check the \u201cDon\u2019t warn me again for this connector<\/strong>\u201d, and click \u201cContinue<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

Retrieving data via the add-on is so easy. You just specify the ticker with exchange, period, and the from and to (which are optional). I\u2019m using the MCD.US ticker for this article because you can access it for free using the \u201cdemo<\/strong>\u201d API key. If you have an account with EODHD APIs, you can access all their data.
\"Power<\/p>\n\n\n\n

It will ask you for your API key. If you access any of the free tier markets, you can use the \u201cdemo<\/strong>\u201d API key as below.
\"Power<\/p>\n\n\n\n

When you click \u201cConnect<\/strong>\u201d you will see a preview of the data. This will give you the opportunity to \u201cLoad<\/strong>\u201d the data as is (which we want to do), or \u201cTransform Data<\/strong>\u201d if you want to make any adjustments on the import.
\"Power<\/p>\n\n\n\n

When the data is imported into a table it will look as below. The table will be called \u201cQuery1<\/strong>\u201d or similar. My advice from trial and error is not to leave it like this. You will want to give your data a re-usable name like \u201cMarketData<\/strong>\u201d. I\u2019ll explain why further down but this is definitely something you want to do. The way you rename the table is to right-click on \u201cQuery1<\/strong>\u201d and you will receive a menu, then select \u201cRename<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

If all goes to plan, it should look like this\u2026
\"Power<\/p>\n\n\n\n

Option 2: Retrieve the data directly from the API<\/strong><\/p>\n\n\n\n

Now this is my personal favourite, and not all that complicated to do. You can try this out by opening the following link in your browser, \u201chttps:\/\/eodhistoricaldata.com\/api\/eod\/MCD.US<\/strong>?api_token=demo<\/strong>&from=2020-07-01&to=2020-10-23<\/em>&fmt=json<\/strong><\/a>\u201d<\/p>\n\n\n\n

I\u2019ve highlighted the parts of interest. \u201cMCD.US<\/strong>\u201d is the ticker for McDonald\u2019s Corp. It\u2019s one of the free tickers you can access using the \u201cdemo<\/strong>\u201d API key. If you want to use one of the subscription only tickers you can use your API key instead of \u201cdemo<\/strong>\u201d. The \u201c&from=2020\u201307\u201301&to=2020\u201310\u201323<\/em><\/a>\u201d<\/em>, I\u2019ve included in italics is optional and allows you to specify a range. The \u201c&fmt=json<\/strong><\/a>\u201d is important as it instructs the API to return a JSON response which Power BI will understand.<\/p>\n\n\n\n

In order to retrieve the data into Power BI, we\u2019ll use the \u201cGet data<\/strong>\u201d menu item. This time instead of selecting \u201cMore\u2026<\/strong>\u201d, we will select \u201cWeb<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

Select the \u201cBasic<\/strong>\u201d option and include your API call (mentioned above) as follows\u2026
\"Power<\/p>\n\n\n\n

This will return the data into the Power BI query editor. It\u2019s mostly ready to go but I would just rename the query on the left to \u201cMarketData<\/strong>\u201d as we did before. Click the \u201cClose & Apply<\/strong>\u201d menu button on the top left and that will populate the Power BI table.
\"Power<\/p>\n\n\n\n

Both \u201cOption 1<\/strong>\u201d using the add-on and \u201cOption 2<\/strong>\u201d using the direct API call will leave us in the same place now. So take your pick which ever you prefer to get you to this point.<\/p>\n\n\n\n

Register & Get Data<\/span><\/a><\/p>\n\n\n\n

Forecasting Stocks Prices in Power BI<\/h2>\n\n\n\n

I wanted to introduce you to some \u201cout-the-box<\/strong>\u201d functionality with Power BI. It will also help you understand the basic components of Power BI and where to find everything.<\/p>\n\n\n\n

On the left of the image below the \u201cClipboard<\/strong>\u201d, you will find 3 icons. The top icon helps you to design your user interface, the middle icon contains your tables and data, and the bottom icon contains your model. We will want to select the top icon to design our user interface.<\/p>\n\n\n\n

On the right you will see three side panels for \u201cFilters<\/strong>\u201d, \u201cVisualisations<\/strong>\u201d, and \u201cFields<\/strong>\u201d. We will be using \u201cVisualisations<\/strong>\u201d first. Under \u201cVisualisations<\/strong>\u201d, there are some great visualisations to help you build your interface. For the forecasting, we will want to add a \u201cLine Graph<\/strong>\u201d. The icon for this is on the second row on the left.
\"Power<\/p>\n\n\n\n

When we select it, it will add it to our viewing pane. You will see it asks for two inputs, the \u201cX-axis<\/strong>\u201d and \u201cY-axis<\/strong>\u201d. Expand the \u201cFields<\/strong>\u201d side panel and drag the \u201cdate<\/strong>\u201d field from our table onto the X-axis, and \u201cadjusted_close<\/strong>\u201d onto the Y-axis. It should look like the line graph below (or similar depending on what data has been used).
\"Power<\/p>\n\n\n\n

We now want to elevate the functionality of our line graph. If you look at the \u201cVisualisations<\/strong>\u201d panel title, under it you will see three icons. We want to click on the one that looks like a magnifying glass. This will provide us with a load more options. The one we are interested in, is the \u201cForecast<\/strong>\u201d. You can fine tune the options later, but you can \u201cApply<\/strong>\u201d the defaults for now. Make sure you toggle \u201cForecast<\/strong>\u201d to on \ud83d\ude42
\"Power<\/p>\n\n\n\n

Now this is the first \u201cgotcha<\/strong>\u201d I noticed\u2026 nothing happens! After some investigation I noticed there was a red cross above my graph. I hovered my mouse over it and I got this error message.
\"Power<\/p>\n\n\n\n

I don\u2019t think Power BI likes an inconsistent trend for forecasting. So how can we fix this? If we look from the beginning of October 2022, there is an upward trend. What we can do is open the \u201cFilters<\/strong>\u201d side panel and drag our \u201cdate<\/strong>\u201d field from \u201cFields<\/strong>\u201d onto the \u201cAdd data fields here<\/strong>\u201d box under \u201cFilters<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

What we want to do is change the \u201cFilter type<\/strong>\u201d to \u201cAdvanced filtering<\/strong>\u201d. Then set the \u201cShow items when the value<\/strong>\u201d, \u201cis after<\/strong>\u201d, \u201c01\/10\/2022<\/strong>\u201d, and click \u201cApply filter<\/strong>\u201d. Not entirely sure how Power BI is performing that forecast, but at least it is working. Maybe it will look better using traditional financial data. I am not convinced this is that helpful for trading data.
\"Power<\/p>\n\n\n\n

Linear Regression in Power BI to Predict the Stocks Prices<\/h2>\n\n\n\n

I wanted to see if there was some other useful \u201cout-the-box<\/strong>\u201d functionality in Power BI to help with trading data analysis\u2026 I didn\u2019t find anything! I don\u2019t think Power BI was designed for that purpose.<\/p>\n\n\n\n

I did however find some exciting functionality in Power BI called DAX. Data Analysis Expressions (DAX)<\/strong> is a programming language that is used throughout Microsoft Power BI<\/a> for creating calculated columns, measures, and custom tables.<\/p>\n\n\n\n

I wanted to see if DAX could be used to perform \u201ca basic<\/strong>\u201d linear regression calculation. In order to complete the \u201cy = mx + c<\/strong>\u201d equation, we will need to perform a few calculations.<\/p>\n\n\n\n

Before we get into that there is something important we need to do. Linear regression won\u2019t like the \u201cdate<\/strong>\u201d column on the X-axis. It needs to be numeric. What we will do is add an index field that will start from 0 to plot the X-axis. Power BI has some built in functionality to do this, but it took me some time to find.<\/p>\n\n\n\n

Click on the third icon at the bottom on the left. Select the \u201cTransform data<\/strong>\u201d menu item drop-down, and select \u201cTransform data<\/strong>\u201d.
\"Power<\/p>\n\n\n\n

Select the \u201cAdd Column<\/strong>\u201d menu item at the top, then \u201cIndex Column<\/strong>\u201d, then select \u201cFrom 0<\/strong>\u201d. This will automatically generate a new column called \u201cindex<\/strong>\u201d that will start from 0. This will form our X-axis.
\"Power<\/p>\n\n\n\n

What will the \u201cindex<\/strong>\u201d represent in our data? Well it could be \u201cdays from 01\/10\/2022<\/strong>\u201d. 0 would be 01\/10\/2022, 1 would be 02\/10\/2022, and so on. When we are done we want to close and apply our transform and return back to the Power BI interface.
\"Power<\/p>\n\n\n\n

Now that we\u2019ve done that we can go back to the middle icon on the left to view our data table.<\/p>\n\n\n\n

We can start preparing our calculation. If we right-click on our \u201cMarketData<\/strong>\u201d table, we will receive some menu options. We will want to create two additional columns using the \u201cCreate column<\/strong>\u201d option.<\/p>\n\n\n\n

The two additional calculated columns will have these formulas:<\/p>\n\n\n\n

\n
\n \n
xsq = MarketData[index]^2\n\nxy = MarketData[index]*MarketData[close]<\/code><\/pre>\n\n                <\/div>\n                
\n