Better share price reports with Excel’s new tool

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood CPA:
Welcome to the Excel Tips podcast. My name is Neale Blackwood. And in this episode, we're going to look at a brand new function in Excel called Stock History.

Now, what this allows you to do is to create reports on share price information. So that information can be the volume sold, the closing price, opening price and highs and lows. It does allow you to create a report with a single function. The Stock History function will spill down and across to create the report based on the inputs. Couple of things you should know. You need internet access to use the Stock History function. It's actually accessing information on the internet.

Also, when you have a file that has the Stock History function in it, you will see a disclaimer at the top of the screen. That will say, financial market information is provided as is and not for professional or trading purposes or advice. And there's a link where you can click to learn more. So just be aware of that disclaimer. But the information that is reported can be daily, weekly or monthly. It's not live. So you're not getting hourly information. So you're only ever getting information on the previous trading day.

Speaking of trading days, if you are going to just get one closing price, which you can do with the Stock History function, the Stock History function has a lot of different arguments, which I'll go through. But you only have to provide two. So the first argument is the code for the share that you're looking for.

Now, you can get information from stock exchanges around the world. In the companion video, we only look at the Australian Stock Exchange. So the code for the Australian Stock Exchange is XASX. Then you use the colon. So then you provide the code for the share you're interested in. And on the companion video, we looked at the Commonwealth Bank of Australia, which is CBA. And so that's text. So that needs to be inside quotation marks. And so once you provide it with the code, you then need to provide it with the start date.

Now, that start date, if that's all you're going to do is give it a code and a start date, needs to be a trading day. If you put a date that's a weekend or a public holiday, then the value error will be displayed. So if you are just listing a single date, then you do need to make sure that is a trading day. Now, it's a bit more flexible than that, though, if you're looking for a range, because the third argument in the stock history is the end date. And if you have a start date and an end date and providing one of them is a trading day, then you will get a report out of it.

So if you are providing the start date and the end date, they don't both have to be trading days. It is fairly flexible and it will show you a date range. So the fourth argument is the interval. So you can report daily, weekly or monthly.

Now, one of the downsides of the stock history function is that that option is a number. So zero is daily and that's actually the default. So if you leave that out, then it will give you daily information. If you want weekly, you use a one. And if you want monthly, you use a two. So this is the same for some of the other arguments in the function. You need to provide a number.

Now, whilst that can create a very short function, it's not very human friendly. And in the companion video, I take you through a technique where I convert text that represents these items. So daily, weekly and monthly into the number that the stock history is requiring. In the companion video, you can see how we can use the match function to extract the numbers that the stock history requires based on looking up a value in a list of entries.

Check out the companion video. It shows you how you can create a interactive report that's really easy to understand and easy to create just using drop down cells.

Okay, so the next argument is the headers. So the default is to show the date and the closing price. And that will have headers there. There is another header where you can add the actual code above the header as well. And so that's under the headers argument.

So after those arguments, you can then define what are called properties. So the property is a number that represents the measure that you want to get. The items that you can bring in are the date and that's the default as well as the closing price, opening price, the high price, the low price and the volume.

Okay, so they are all properties. And again, they are all represented by numbers. Close is one. Open is two. High is three. Low is four. And volume is five. And so you can add as many of those to the report as you like. You just include them as extra arguments. So you can only just show a couple or you can show all of them. It's up to you. So it's really flexible.

The stock history function will spill down and across to create the report. So one single function will create the report for that particular share. Now, something to make things a little bit easier in this companion file was to bring in a listing of all of the stock exchange names and codes.

So the ASX has a CSV file on their website that you can download. So I used Power Query in Excel to import the CSV file. So that allowed us to have a drop down to select the share name. And then I used XLOOKUP to bring in the code that we need for the stock history function. And so that file can be updated. Just right click on the output and refresh it. And that will give you the latest information that's on the ASX website in that CSV. And the companion video took you through the location of that CSV file.

Now, once you've created the report, you might want to try out what's called the stock chart. Excel has a stock chart. It has a number of them. And the one we used in the companion video has a column chart for the volume.

And then it has another section above that to show you the price movement. So there is a box also included on the same period. And that box represents the price movement. If the box is white, then that means the price increased. And so the bottom of the box or the line at the bottom of the box is the starting price. And the line at the top of the box is the closing price. So it's increased. If the box is black or dark, then that means the price fell. So the line at the top is the opening price. And the line at the bottom is the closing price. As well as the price movement, there's also the highs and lows plotted on there. And that uses a line.

So if you have a long line, that means there's a big difference between the high price and the low price for that period. The monthly stock chart sort of is the best one to use. You could use a weekly one, but obviously the monthly one has a lot more transactions in it. So check out the stock chart. It does provide a lot of information on one chart that's not too hard to read.

If you do find the stock chart a little bit overwhelming, then I do provide a what's called a combo chart, which plots just two things. So it plotted the volume and the closing price. So you can check out the companion video to see how to create a combo chart. So the combo chart has two axes. The stock chart does as well. So the stock chart has an axis for the volume and an axis for the price information. And the combo chart also had that.

So you're able to plot volume, which obviously is a very high number, as well as the prices, which are usually a lot lower than the volume numbers. So as well as creating an individual chart for a particular share, you might want to look at multiple shares at once. And so the Sparkline chart can be a solution to that.

The Sparkline chart allows you to create a chart in a cell. So you can have a list of all of the stock codes and then have a Sparkline chart next to each code to see the movements over the period. Now, Sparklines tend to want horizontal data, whereas the stock history data creates vertical data.

So each row has a date in the normal stock history report. But you can convert a vertical report to a horizontal report using the transpose function. So that switches a report that's going down row by row. And that will change it to go across column by column.

In the companion video, I show you how you can convert that vertical report into the horizontal report, which then makes it a lot easier to create the Sparkline charts.

Now, just a heads up on Sparkline charts. When you look at them, the prices may appear fairly volatile. And that's because in the default Sparkline line chart, the vertical axis doesn't start at zero. And so that can make the changes look a lot more than they are. So just a heads up on that. In the companion video, I show you how to change that vertical axis to zero if that's what you need to do.

One thing worth mentioning is that Sparkline charts currently don't update automatically for spill ranges. So if your spill range does change, you'll need to manually update the Sparkline range, which is demonstrated in the companion video. When you change one of the arguments in the stock history function, you might notice the message at busy in the cell. This means that the function is just updating the information from the Internet. So your internet speed may affect that. So the stock history function allows you to create a report with a single formula that can show you the share price, the share volume, the highs, lows, etc.

So the stock chart is also worth checking out. It does provide a lot of information in a single chart, and it works perfectly with the stock history function.

Hope you found that useful. Thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.