Stock Quote App - Dynamic, Fast & Easy In Power BI

Stock Quote App – Dynamic, Fast & Easy In Power BI

2 comments

In this blog, I’ll show you how to create a dynamic stock quote app in Power BI. This interesting technique came out from a post in the Enterprise DNA forum. You may watch the full video of this tutorial at the bottom of this blog.

The member wanted to know how we go about starting with online data and pulling that into Power BI and then analyzing it from there instead of starting with what we typically do, which is an Excel file or a CSB file.

Similarly, along those lines, I’ve got a question from another forum member about pulling information off of the Yahoo finance site. And so, he and I worked through an interesting solution on that.

It’s a dynamic solution where you can pick whatever stocks you want, and it’ll give you that High/Low/Close chart. You can pull in any granularity you want in terms of daily, weekly, monthly, and annually.

stock quote app

And so, I wanted to show you how to do this and how easy it is. It shows how powerful Power BI is in terms of web scraping and automated data poll capabilities.

Instead of doing what I normally do, which is to work through the demonstration in an edited way and kind of hit the highlights, I’ll show you from start to finish. We’re going to build this from scratch.

Pulling Online Data Into Power BI For The Stock Quote App

We’re going to start with an empty file and the only thing I’ve done to it is I’ve added two custom visuals. I’ve added that High/Low/Close candlestick chart and a chiclet slicer, which is one that isn’t necessary, but I like it better than the default slicer. You can see that there’s no data yet and no visuals as well.

I’m putting 15 minutes on the clock and then I will build and visualize this stock quote app in under 15 minutes. Let’s start at the Yahoo Finance site and do a Quote Lookup.

It doesn’t really matter what quote we pull and you’ll see why in a minute. So for this example, let’s pull Netflix.

Let’s go down to historical data and we can choose whatever granularity we want. Let’s say we want three months and we want to pull that for the weekly close, just so that it doesn’t overwhelm the High/Low chart. Then, click Apply.

And then instead of clicking download, we right-click and copy link address since we want to pull the online version.

stock quote app

Inside Power BI, we go to Get Data, and we’re going to pull this through a web connector.

stock quote app

We can just use the standard basic web connector. We don’t need to do anything fancy here. We just paste the copied link and hit the enter key or click OK.

stock quote app

You’ll see that this pulls it in really nicely and we don’t have to do anything. We’ve got all we need here.

stock quote app

Transforming Online Data

We then go into Transform Data and into the Advanced Editor. It’s pretty compact for what it’s doing, but the thing we want to look at here is the Netflix symbol (NFLX). This is the static part of it that we don’t want to have to pull that quote for each stock that we want.

stock quote app

So we’re going to turn this into a function. To do that, we’re going to say StockQuote “as text“, and then “as table” since we want a whole array of quotes to pull, and we want to iterate over that table. Finally, we just put the arrow, which turns it into function.

stock quote app

And now, we’ll replace this static part with our parameter. And now, we’ll replace this static part with our parameter, then click Done. This will take that URL, and then each time it iterates, it’s going to pull the stock quote from the table.

stock quote app

We can then change the name of this function. I’ll call it Quote Pull.

stock quote app

Creating A Table Of Stocks

The next thing we need to do is to create the table of stocks over which the function is going to iterate. And we’re going to do that by pulling that in from Excel.

In Excel here, we have a file called StockList and we have a column called Quote Symbol, which matches our parameter name. Now let’s take the basket of stocks that we want to pull and then save the file.

stock quote app

Inside Power BI, we go and pull that file in.

And this is how it looks.

Now we can transform that. Note that we need to Use First Row as Headers. Let’s then change the name of this file to StockList.

stock quote app

Iterating The Quote Pull Function Over The StockList Table

We want to iterate that Quote Pull function over our StockList table over each of the stocks that we’re watching. So we go to Add Column and click on Invoke Custom Function.

stock quote app

Let’s call this Pull column and the function query is going to be our Quote Pull function. The columns that we’re going to iterate over is our QuoteSymbol.

stock quote app

So now we have a table for each of the stocks we’ve got in our StockList table.

stock quote app

Then, we make some adjustments here. By clicking on the expand button, we go and uncheck the Use original column name as prefix.

Changing Column Type Accordingly

The last thing we have to do is to change the column types and make sure that they are appropriate to their respective columns.

When that is done, we go to the Home tab and click Close & Apply.

Building The Dynamic Stock Quote App Graph

And now our web scraping is done, let’s start doing some graphing. Here we use the candlestick table and just adjust the size to fit into the page, as it is the focal point of the page.

stock quote app

Then, from the Fields, we drag our Date, Open, Close, High, and Low fields into the table. And you can see how they all come together.

stock quote app

Next, we turn the High/Low caps on and then format our Date.

stock quote app

Now all we need to do is throw our chicklet slicer in with our Quote Symbol.

stock quote app

We can go into the General option here and make some more adjustments. Let’s put a bunch of columns in here, say 15 columns if we have a lot of stocks.

stock quote app

We can also adjust the height or width if we want, and then turn the Multiple Selection off and the Forced Selection on.

We could also change the size and color of the chicklet to make it look better. This is the final look of the dynamic stock quote app.

stock quote app

Conclusion

In just a few minutes, we built a full stock quote app and made it dynamic. We’ve graphed it out and it look great.

If you really want to get fancy with this, you could go back to that URL and you could change the granularity parameters. You could also add more slicers if you want, and more.

I hope you enjoy this and find it useful. Hopefully, you’ve learned something from this and will be able to implement it in your own work environment.

All the best,

Brian

Membership Banne

***** Related Links *****
How The Query Editor Works In Power BI
Advanced Transformations In Power BI
Best Practices For Transforming Data In The Query Editor

***** Related Course Modules *****
Business Analytics Series
Advanced Data Transformations & Modeling
Financial Reporting w/Power BI

***** Related Support Forum Posts *****
Extracting the Web Data from Multiple Pages and Tables
Collecting Data Online
Yahoo Finance API Setup in Power BI (Stocks & Shares)
For more collecting online data support queries to review see here…

2 comments on “Stock Quote App – Dynamic, Fast & Easy In Power BI”

  1. Mega thanks Brian. It’s already in use. I used an Excel multi stock downloader for some 150+ tickers. And the same for dividends (over 3 years as well). I will still do that because it’s memory efficient. It producers .csv files for each one and then I upload the folder to my BI file. Works nicely for monthly updates. But now, with your help I am pulling just yesterday’s prices and I can do a nice date related comparison. The 1d pull works OK, but it keeled over when I tried to pull 3 years worth of date for the 150 stocks! So the combination of the 2 approaches works very nicely. Thanks for your clear explanation and I now understand how to create a function! I sort of half knew but now I know! Mega thanks for all you do to help us lesser mortals!

    1. Hi Brian – just wondered how I could make the date range dynamic – always get yesterday’s data or n days data? Right now, it’s historic and I would have to go into Yahoo and then into the QuotePull function to update the date range. Any ideas?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.