Tracking Stocks With Numbers On Your Mac

You can track and chart stocks in Numbers on your Mac with the STOCKH function. You can get the close, high and low prices for a stock on different days and then use formulas and filters to build various charts. You can then easily change the stock symbol and dates to generate new charts.

Video Transcript: Hi, this is Gary with MacMost.com. Today let's build a Stock Chart using Numbers on your Mac.
MacMost is brought to you thanks to a great group of more than 750 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So if you want to analyze stock histories using Numbers you can do that using the STOCKH Function. Let me show you. Here let's start with a blank template and I'm going to use a simple function to get a stock price. I'll hit the equals key here and if I search in Functions for stock you can see I come up with two things. STOCK and STOCKH. STOCK will give you the previous days closing price. STOCKH is much more powerful and will give you the closing price and also things like highs and lows for any date. So I'm going to double click it here to insert it in and we can see the parameters.
So first we can choose the symbol. I'm going to use Apple as an example. You have to put the symbol in quotes so double quote AAPL for Apple as the symbol. Then we choose whether to look at the close, open, high, low, or volume. You can see these also have numbers so we can use the number instead. Let's choose close. Now let's choose the date. We put this in quotes as well. So a double quote here and we'll look at say 1/2/2020. Close the quote. Now I'll hit Return and we'll see the closing price for Apple stock on that day.
Let's go back in here and let's look in the Functions Help for a second. We could read lots of different things here about the STOCKH function. So if you want the details read through this and look through the examples.
Let's create something a little more robust. Let's make it easy to change the stock symbol and also the date. So I'm going to create a new table here using this style. It's going to put it below. But I'm going to move things around and put it above. So I'm going to move this one here to the top and this one here below it. I'm going to shrink this table so it's only one column wide and only has one extra cell after the header cell here. So let's take a closer look at this. 
Here I can select this table and let's go to Format, Table and turn off the title. Let's make some more room here. Move this down a bit here so we can see both cells clearly. Let's put Stock Symbol here and I'll put the symbol there. 
So now I can go into this formula here and instead of having the Apple symbol hard coded in there I can delete that and I can click on this cell here. I'm going to additionally click here and set it to Preserve Row, Preserve Column. So use the Absolute location of this cell. So no matter how I copy and paste this function in the future it will always refer to this exact cell. So now it's going to get the symbol from here. So now I can switch to another symbol and it instantly changes
Now let's also put the date here. So 1/2/2020. Let's go in here and instead of hard coding the date in there I'll delete that and I'll refer to this cell. So now we've got it like that. Let's name this column Date and this column Close. Now if I were to put another date here, like 1/3/2020, and copy this formula from this cell and paste it here, it will pick up the same stock symbol but the new date. I could now extend this if I want. I could select both of these cells and then if I move my cursor over the bottom I get this yellow dot. Click and drag that and you could see it extends it out. So I could select this formula here and paste it in here. Now I get the closing prices for these days. 
What about these errors here? Well, these are non-trading days. These are weekends. So we get an error because there's no closing price on Saturdays and Sundays or any other day the market it closed. Don't worry too much about those. We'll deal with those in a minute. Let's go ahead and add other columns here. I'm going to do a Low and a High. I'm going to take this function and paste it into both places. But for the Low I'm going to go into it and change close to low. That's the low price for the day. In here I'm going to change close to high. That's the high price. If I Copy these two and paste them throughout here I now see the Close, Low, and High for all of the trading days.
Let's extend this here by dragging down the table to include the entire month. Remember we started on the 2nd. So let's actually create this the proper way by going all the way to the 31st there. Let's do 1/1/2020 which of course is not a trading day, it's New Year's Day. I'll extend this yellow dot here all the way down to the bottom so it goes 1/1 to 1/31. Now if i take these and I extend this as well it'll put those functions on each of those. So now we have the close, low, and high for everyday in January.
Let's get rid of the cells here on the right and I'm going to select of these cells here and drag the line here at the top over to the left to shrink them so we have plenty of space here. Now let's create a chart to track the closing numbers. So I'm going to click here, click the column B there, and click Chart and we can create a simple line chart here. Now we can see the closing numbers for Apple stock throughout the month of January. Notice the breaks. The breaks are those non-trading days. 
Let's get rid of the non-trading days. What I'm going to do is go back into these function here. I'm going to surround them with the IFERROR function. So IFERROR and then the second parameter after looking at the value there is whatever we want to use as the error value. So in this case I'm going to put a dash. Double quote dash double quote. So if this function produces an error we will see the dash instead of the function. So now you can see it looks a little bit better. Let's do it for each one of these as well. I'll do IFERROR and then at the end here dash and I'll do it here and now let's spread that function all the way down here. Now instead of errors we get dashes. 
Now what we can do is Filter. Let's use the closing column there as a Filter. Go to Organize, Filter and then add a filter. Say a filter on close and say If the text is not, and let's do a dash. So it's going to filter out all the rows where the dash is there instead of a number. Now you could see it filters it out. We've got the gaps filled in so it goes the 2nd, the 3rd, and then it jumps to the 6th. So we only have valid numbers there. The chart changes to reflect that. Now we have a coherent chart that doesn't have those gaps.
What if you wanted to chart the highs and lows. Let's try that. So I'm going to take this chart here and I'm going to move it down and out of the way. What I would like to do it chart the low and the high and maybe do it in a stacked bar chart. But a stacked bar chart by itself won't work because we want to stack 75 on top of 73. That wouldn't give us anything useful. What we like to do is stack the difference between the high and the low on top of the low. 
So let's create a new column here. I'm going to drag this out call this one HL Dif. It's just going to be a simple formula equals this number, the high minus the low. So you can see here the high is $1.35 above the low. Now let's populate the whole column with that. Now we can do a stacked bar chart with the low and the high low difference. I'll select those two columns. Do Chart and then that there. Now we could see the low, of course, takes up the majority of this, and then the high is this little bar above it. So what if we got rid of the low. One way to do that is click on it. You could see that I could select just a Series. It shows here Format, Series, Value low. I could switch to Style and then I could change the style. I could make it white. That would make it go away. But instead I'm going to go here on the Color Wheel and just leave it at the current color but make it completely transparent by bringing the opacity down to zero. So now you could see just a bar here that shows the low to the high.
Now this is looking pretty good. What will be even better would be if we could see the low, the high, and the closing price. You'll often see that as a line here on these bars. I'm going to move this chart further down and move this one down. Now let's do another stacked bar chart. So we're going to need some new numbers for that. I'm going to create two new columns here. Now we want to make sure when we work on these functions that we turn OFF the filtering. I want to show you why. If I turn it off notice that the cells that weren't visible before don't have anything in them right now. That's not going to be good for something that we're going to do in the future. So I want to Copy one of these functions here and paste it throughout. I get the errors there but I'm not worried about it. I could, if I want to, IFERROR and then do that same dash in there. Copy and paste it throughout.
Now these two new columns are going to be the1% Max and Close 2. So what are these for. Well 1% Max is going to be 1% of the highest high. So we can do that by taking the maximum value of the High column and multiplying it by point zero one. That tells us that basically the maximum value is about $82 and 0.82 is 1% of that. We could just copy and paste that throughout the entire column here. That's actually an expensive calculation. Calculating the maximum value. So instead I'm just going to do it in the very first row. Then in the second row I'm going to set it equal to that value. I'm going to make sure I set Preserve Row so it's always looking at the first row. This is a simple inexpensive function just to basically take this value and put it here.
So I'm going to Copy this and paste it in the entire column except for that first cell. So now I get 0.82 throughout the entire thing. What is Close 2. Well, Close 2 is going to be equal to the Closing price minus that 1% Max. So let me go and Copy and Paste throughout the entire thing here. So instead of 75.15 Close 2 is 74.27. The close minus that 0.82. Minus that 1% of the maximum line. So why did I do all of that? Because I can create a really cool chart using this Close 2 and this 1% Max. Let me got and select the two of those. Create a chart, a bar chart. Now it's going to put the 1% down there at the bottom. So I'm going to switch those and go to Series here and select this Series and instead of 1% Max I'm going to change that to point to the Close 2 and instead of the Close 2 there I'm going to change that to point to the 1% Max. So now what we've got is these equally sized little bars, because they are all 0.82 high, above the Close minus that 0.82. 
So I'll select this Series here. I'll go to Style. I'll change its color to be transparent just like before and you get these cool little bars. Let me go in here and in the Chart turn off the Legend at the top. I'm going to move this here so I can see both of these charts at the same time. For this one I'm also going to turn the Legend off. For this one I'm going to set the color. So for this Series I'll click there on that Series, the style to black. So it's a little black bar.
Now let's go and turn the Filtering back on. So I'll select this table. Organize and turn the Filter back on. Now we see one chart here that shows you the little bars with the Closing price. Another that shows you the highs and lows. All we need to do is drag one on top of the other and make sure they are perfectly on top of the other. Getting them perfectly on top of each other is tough. So I'm going to Undo that and instead I'm going to use Numbers to do it. I'm going to go to Format, Arrange and with this selected I'm going to see here that the position is 477. Let's set that to something even like 460 and let's change the y position to say something like 60. So it's down there. Notice the width is 340. The height is 247. Let's make sure that those are full whole numbers here by changing it to something like that. There. So there are no decimal points that are hidden. I'm going to change this one to 340, 240 and then 460, 60 so it overlays perfectly.
So 340, 240 and 460, 60. Now these are perfectly overlaid on top of each other. You can even see the numbers are overlaid on top. The black bar now represents the Close and the green is the lows and the highs. That's pretty good. What would be even better is if the dates could be changed because right now I would have to go in there and just manually change these dates to something else. Let's go in and extend this table here with two more columns. 
Let's call this the Start Date and the End Date. Let's say the start date will be January 1, 2020. The end date will, for now, be January 31, 2020. Let's go into this table here and turn Off the Filters. We can edit everything easily. Now let's go and change this date here to be equal to exactly this date. Let's go and set the Preserve Row and Preserve Column. Now let's remove the rest of these dates, delete them. Okay. What we want to do here is this date should be 1+this date. So equals this date +1 and look how that works. Now you would think I could just keep going with that. Right  But I want there to be this end date. When I get to January 31st I don't want there to be a February 1st after it. 
So instead I'm going to alter this function and do an IF and check this date and see if it's less than or equal to this date. Set that to Preserve Row and Preserve Column. So it's always looking at. If it is less then I want to use that same number, so in other words this value +1. If it isn't then I want to put a dash or something like that there. So now you can see that works there. I can extend this down and you could see how the dates fill in. Now what happens if this is a much longer table here. If I were to take this and keep extending all the way down you could see here the 31st is there but I get a dash for February 1st and after that I get an error because it's trying to add one to a dash. 
So the result is the same. I don't get anything here. Remember as long as Close has a dash in it, it won't be shown. Let's extend this table quite a bit. Just drag this down, way down. If I go 1000, that's like three years. 365 x 3. If I go even more you know there's like four or some years. Then you could see how it's going to fill in those formulas there. So now I can extend this quite a bit. So I could say I want this to go to March 31st. So look what happens. I start there on the same day and now I go down and after March 31st it's just going to be dashes and errors. 
Let's select something in this table and I'll go to Organize and turn the Filters back on. That will get rid of all the dates that Closes are dashes. So the table is only to March 31st. After that everything is filtered out. The charts change to reflect that. You could see here the highs and lows and the Close for all the dates from January 1st through the end of March. Now we need to change these dates to reflect the changes. So if I want to go to August 30th all I need to do is change the date and wait for everything to recalculate. I can see my charts change to reflect that. If I were to change this to a different stock I could do that as well and then wait for everything to recalculate again.
So I hope this gives you some idea of what you can do using the STOCKH function in Numbers and how you can create some charts to analyze stocks with it.

Comments: 12 Responses to “Tracking Stocks With Numbers On Your Mac”

Todd Smith

4 years ago

I have used that STOCKH function set to get the closing price every Friday. I find that many times when I open the spreadsheet after the market closed the prices are not updated on my sheet PRices do not update until sometime on Saturday. Ami missing something?

Gary Rosenzweig

Todd: Maybe it is previous day's closing, so it won't show. Also, maybe trying changing a value (some extra cell) toileting get the formulas to recalculate?

Tim A

From Numbers Notes
"If you enter a formula with STOCKH when the internet is unavailable, STOCKH returns no value. However, when the internet becomes available, the formula updates with a value returned by STOCKH."

I just experimented and found Numbers far more 'intelligent.' Once I had a successful download I could go offline and the stock values were retained. More surprisingly I could look back 6 months and the cell populated correctly!

Maury Cralle

How can I grab all my stock symbols from by broker account and insert them into Numbers so they only go one per cell?

Maury: Hard to say without knowing the details. But look for an export function from your broker and get a CSV file, or Excel file. Or, just copy and paste. If pasting doesn't work, then paste into TextEdit (plain text) first and clean it up with one per line, then copy and paste from there.

A nice way to see all the details of a function instead of trying to scroll through them of to the right (1:36) is viewing them in:
Menu/Help/Formulas and Function Help
which takes you to a full page display in your browser...https://help.apple.com/functions/mac/9.1/

David Barnet

3 years ago

I am trying to add RDS.A using Stock in Numbers to get the name and updated information. It won't populate, since RDS.A is a Holland-based company to I need to add anything to the Stock Symbol to get it to populate?

David: Well when I did a search online for RDS.A it came up with the Yahoo stock price for it. There the symbol is RDS-A, if that is the same thing. I tried "RDS-A" in Numbers and it gave me a price.

Joseph Snow

Thanks for the great videos; makes my Patreon donation an easy choice to continue supporting your work!
I started with the Portfolio example template from Pages, which seemed to be a good place to start loading
Looking at the STOCK function, I would like to add a column for the 52 week high, which is supported by STOCK. However, when I try to add that column, I get an error message that states "This formula can’t reference its own cell, or depend on another formula that references this cell."

Joseph: hard to know what could be wrong without seeing the formula. What is it?

len

Thanks! but how do we add/locate stocks trading on TSE? can only find NASDAQ stock.

len: Just find out the exact characters used to represent the symbol on Yahoo Finance.

Comments Closed.

Tracking Stocks With Numbers On Your Mac

Comments: 12 Responses to “Tracking Stocks With Numbers On Your Mac”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information