Create Stock Charts in Excel Using Power Query

In Excel, there are multiple different stock charts you can create. All you need is some combination of the date, opening price, high price, low price, closing price, and volume to generate what you need. In this post, I’ll show you how you can utilize Power Query to pull that data in and transform it, without having to apply any manual changes to it every time. For an overview, you can check out this post on How to Get Stock Quotes From Yahoo Finance Using Power Query. I’m not going to repeat those steps and will assume that you’re familiar with that process.

In this example, I’ve downloaded the stock prices for Apple (NASDAQ:AAPL) for the month of October 2021:

This already has all the data that is needed to create the four types of stock charts in Excel:

  • High-Low-Close
  • Open-High-Low-Close
  • Volume-High-Low-Close
  • Volume-Open-High-Low-Close

The key to making these different charts is just ensuring that you’ve got the correct fields in your download, and in the right order. For the High-Low-Close chart, you only need three fields to generate the following chart:

A high low close chart in Excel.

If you’re creating the open-high-low-close chart, all you need is to add the open field to the data set:

An open high low close chart in Excel.

And for the volume-high-low-close chart, it’s just the volume instead of the open that goes at the start, and then you get something that looks like this:

A volume high low close chart in Excel.

The last chart includes both the volume and the open before the high, low, and close values:

A volume open high low close chart in Excel.

These charts are fairly straightforward to generate once your data is in the right order. But rather than moving around different fields, you can make all the changes within Power Query so that right when your data loads, it’s in the correct format.

Using Power Query to adjust your download

To modify an existing query, go to the Data tab and select Queries & Connections. Off to the right, you should see your query, where you can right-click and Edit it:

Editing a query in Excel.

The first thing I’ll edit is the date range. In my earlier post, I just downloaded the full year of data. But if I want to filter only for October, then I can click on the drop-down for the Date field and select Date Filters to filter Between a range of dates.

Using the date filter to filter dates in power query within a specified range.

Using the calendar icon, I can specify the range of dates I want to include in my download:

Filter rows in power query based on their date values.

Next, if I want to just include the data for the most basic chart, the high-low-close chart, I’ll right-click on the Open, Adj Close, and Volume columns to remove them. Then, I’m left with the following:

Power query stock download that only contains the high, low, and close fields.

Now, if I were to load the data in Excel, I would already have all the columns I need to create the chart:

Creating the high low close chart in Excel.

Note: if you want to get rid of the gap in dates on the chart, click on Format Axis and for the Axis Type, select Text axis:

Converting the date axis in Excel so that it reads as text instead of as a date.

This prevents Excel from trying to fill in any missing dates from your data set Another thing you may want to do is format the date field so that it is a custom format showing MMM DD so that it saves space:

Stock chart in Excel with custom date formatting.

Now, let’s go back into Power Query and this time create the more complex download, for the volume-open-high-low-close chart. I’ll start by removing the last step I applied which removed more columns than I need for this current download. To remove any steps in Power Query, click on the ‘X’ next to it:

Removing the last step in Power Query.

In this example, it’s just the Removed Columns step I will eliminate. The only column I need to remove from the download this time around is the Adj Close. So that’s what I’ll do, right-click and remove that column. However, my table still is not in the correct order:

Power Query table after removing the Adj Close field.

The Volume column needs to go before the Open column. This is as easy as just dragging the column and putting it in front:

Power Query table after moving the Volume field.

Now that it’s in the right order, I can load and close this into Excel. And now, the volume-open-high-low-close chart can easily generate:

The volume open high low close chart in Excel.

Suppose I want to adjust my chart to include data from September as well. Again, I can go back to edit my query. This time, I’ll select the gear icon next to the Filtered Rows step:

Modifying a Power Query step using the gears icon.

There, I can just modify my date range using the calendar:

Modifying the date filter in Power Query.

Now, re-loading the data into Excel automatically updates my chart with a simple refresh:

The volume open high low close chart in Excel using a broader date range.

The beauty of this is this query will update with new data and your chart will also update, taking out the manual steps of having to make any changes yourself. And if you incorporate named ranges like in my post covering pulling stock prices, then the data will easily refresh based on the variables that you’ve entered.

If you liked this post on How to Create Stock Charts in Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Add a Comment

You must be logged in to post a comment