Track Your Stocks In Mac Numbers

Hi, this is Gary with MacMost.com. Let me show you how to build a Numbers spreadsheet to track your stock portfolio.

MacMost is brought to you thanks to a great group of more than 2000 supporters. Go to MacMost.com/Patreon. There you could read more about it. Join us and get exclusive content and course discounts.

Now the main purpose of this tutorial is not necessarily to build the perfect stock portfolio tracking spreadsheet. It's to teach you some Numbers skills. If all you want to do is just quickly track your stocks in Numbers there is already a template for that. Go to Template Chooser in Numbers. Go to Personal Finance and you'll find My Stocks. Go into that and you can easily just add your stocks here and track them here. There is even a 30 day history with a chart. But instead let's create our own from scratch so we can actually learn how things work.

So I'm going to choose the blank template here and create a new blank spreadsheet. I've got the default table here and we'll just use that to start building. The Header Column here is probably a good place to put the stocks symbol. So I'll just call this Stock. Here's where I'll actually have the symbol. So let's start off with a sample one. We'll use Apple as the sample symbol. The next column we're going to go and access some stock information. There's a function in Numbers that allows you to get all sorts of information about stocks. We're going to use it right away here in the first column. So I'm just going to call this Name. We're going to get the name of the stock because sometimes the symbol doesn't really reveal what the stock is. So it will be useful to actually have the name as well. So I'm going to use the equals key here to start entering a function. Then when I look here on the right I can see the functions Help and I'm going to look up Stock. There is Stock and StockH. We're going to use Stock here all the time. When I select that I can see the Help information here about Stock. There is a lot of different things this can do. So let's start off by typing Stock here and I'm going to select it from Suggestions here. This will fill in the parameters with placeholders. So now I can select Symbol and I can either type the symbol or I'm simply going to select the symbol from the Header column here. So it is going to take whatever is in the first column and use that for the stock symbol.

Then we have which piece of information we need. Let's click on that and we can see all the pieces of information here. For this column I'm just going to simply get the name. So I'm going to choose Name here and then click the Green Checkmark. Now you can see it correctly figures out that AAPL is Apple, Inc.

So now we're going to enter some information that the stock function can't know. It can't know how many shares we purchased and when. So let's start off with Purchase Date and then here could be some date. Let's go ahead and say May 1, 2023. Then the next column let's go and have the purchase amount. So how much of the stock was bought, 100 shares in this case for example, and then let's do the purchase price which isn't necessarily the opening or closed price on that day but could be any price in-between for that entire day. So, we're just going to come with a sample amount here and I'm going to say 100 for $100. It may not actually have been $100 or anywhere near that on that day, this is just an example. So now I've got all of this. What would be great to do is actually calculate the value of this purchase. So we can do this with a formula here. We're going to do Purchase Value. The purchase value is very simple. We're going to type equals here to start entering a formula and it is simply the purchase amount and I'm going to use asterisk for multiplication times the purchase price. Then we get that 100 shares purchased at $100 is $10,000. Simple multiplication here.

Now let's get some nicer formatting here. Notice Purchase Price and Purchase Value just gives us a number. It doesn't give us a dollar value. So I'm going to select both these columns. I'm going to Shift Click to select the range here and I've got both of these columns and under Format Cell I'm going choose the data format of Currency. I'm going to set zero decimal places and add a thousand separators so it looks a little nicer there. A little easier to read. So we've got those now there and this will be the format for all of the cells here in these two columns. So as we add more stocks we'll get dollar symbols here.

Now the next thing we want to add is another column for the current price. Now for this we're going to use the Stock function again. So i'm going to do equals and then Stock and then select this here, the symbol is again going to be the first column there. Then I can choose either Price or Previous Close. So here's where Numbers kind of falls short from other spreadsheets. Numbers will only give you the previous closing price. It won't give you the price during the day. Other spreadsheets, like Goggle Sheets, will give you, I think, like 20 minute delayed price. Fortunately everything I'm teaching here should work pretty much the same in Goggle Sheets and even Excel as well. So for Numbers really this is only good for tracking your stocks from day-to-day which may be all that you need. But not for tracking in the middle of the day. Even if you were looking for that, having a 20 minute delayed price probably isn't what you want and you've got some sort of professional software and are paying for that to get the actual current price.

So I"m going to choose Previous Close here and use that. If I had chosen price I should get the same number. But what I'm afraid about is that since Apple, at one point did give use a 20 minute delayed prices, they might switch to that in the future. I don't want to change the values here. I want to just stick to having basically a daily price for this column. So Previous Closing price is right there. Now I can go ahead and add another column. I'm just going to grab the handle and drag it out, and I'm going to get the current value. Which is easy to calculate. It's going to just be the current price times the Purchase Amount. So 100 in this case. That's the current value. So this is how much it is worth right now. I can select these two columns too and even though they are automatically formatted with dollar signs because I'm using the stock price here I can still go to Format, Cell, and say no I definitely want it to be currency and I want to get rid of the decimal places and add the 1000 separator there.

Now what would be nice to actually add here is how much was gained. This is pretty easy to do. I can actually add another column here and I can go the Gain here and it simply is going to be the Current Value minus the Purchase Value. So that amount there. I can do that as a percentage. Let's add another column and let's call this Gain% and that's simply going to be amount gained and then divided by the Purchase Value. So in this case it gives us a 1. We want to make sure we set the Format Cell to percentage and then we don't want to have any decimal places here. So in this case it is 124%. Which makes sense. We purchased $10,000 worth and it is now $22,000 worth so it gained $10,000 which would be 100% plus a little bit more 124% gained. The current value is the same as the purchased value. The gain would have been zero and this would have been a zero percent gained.

So you can continue to add more things if you like. For instance, I can use the Stock function here and I can grab this symbol and then I can choose one of these other things here. So, for instance, if I wanted to have the Market Cap or Volume or something else I can choose one of these. Let's use Market Cap here. There's the Market Cap. This number, of course is a currency and it's huge. If you want to have something more reasonable you can actually go to Data Format and create a Custom Format here. Choose this and then choose a Scale. So I'm going to drag the scale in here and then say I want it to be billions, like that. Also add a currency amount just before this. So now I've got this number here in billions. So I can say, you know, Market Cap and indicate that it as billions.

Now the cool thing about this is that it is now easy to track other stocks. So let's say that you've got some Microsoft stock. All you need to do is Copy and Paste all of this here and you can see it's got the Name, it's got the Current Price and all of that. You need to go and change the Purchase Date to whatever that is. Let's make it June 1. Maybe the Purchase Amount is 50 shares. The Price was $300. Then you've got everything else filled in using formulas. So here you can see it's 48% gain. Let's add another one here and let's do an example of, say, a loss. So I'm going to put this in here and let's say 250 shares at, let's say, you can see the Current Price there is $19 a share. Let's say you purchased at $25 a share. So you can see the purchase value and the current value and now the gain is negative. The amount is negative right there.

Let's select these two columns here and then go to Format, Cell and select Conditional Highlighting. Let's add two rules. So the first rule is if the number is greater than zero then we're going to select green text. Then I'm going to add a second rule that if the number is less than zero then I'm going to set this to red text. This is for the whole column there. So notice what this does is under the two gain columns here it gives me a red number when it is negative. So I lost something. It gives me a green number when it is positive.

Now let's shrink this table by grabbing a handle at the bottom and have no blank lines. The neat thing then is you can add new stock very easily by dragging the bottom here and it's going to add all the formulas in but leave little blank areas there. So let's add in another stock, like that. Let's then fill in these things here to let's say 10/1/2023 and Purchase Amount 100, Purchase Price $100 and now we've easily added in another stock.

So this is my Stock Table. Let's say if I wanted to summarize some things. Let me move this down a bit and add another table here. I'm just going to use a completely blank table with no Headers or Footers. Let's bring it up here to the top. I'm going to shrink it down to just be two columns wide and just three high, like that. So this one here I want to have a Total Portfolio Value. So how would I calculate that? Well, the formula would just be the Sum of everything in the Current Value column here. So I just click there and you can see it says the Stocks Table, because I named this Stocks, and just the column H. That gives me the total there. I can do, also, the Change Amount which is going to be that Sum of the Current Value minus the Sum of the Purchase Value right there. So you can see here that's how much it has gained. I can do Change Percent by basically taking this number here and dividing it by the Sum of the original purchase value. If I select that cell there the format cell, set it to percentage and then let's have no decimals, then I could see the change percent. So I can get rid of the table title here or I can just say Summary. I can even select all these cells here. Then you go to Format, then Cell, under Border select the one here that shows all of the borders and change the corder style to No Border there. Now I've got no border at all. I can take these cells here and have them be right justified and these cells here and have them be left justified. I can continue to say like maybe Command B to Bold those. You know I can color this up a lot of different ways. I can select all these cells here. Let's make it a little bit bigger like that. So you've got some general information here and then all your specific stuff.

The last thing I want to show you here is a chart to show you how your portfolio is divided up. So the column that you want to worry about here is the Current Value. If you take this, select it like that, and go to Chart and then create a Pie Chart, you're going to see the current value for each of these stocks. So I can see here, very easily, it gives you can idea of how your portfolio is divided up. We can go to Format, Wedges here, and we can add data point names. We can see them right in there. We can do the value as either percentage or we can do currency and actually see the amount. I can also go to Chart here and I can get rid of the legend since I've got the stock symbols right in this. So you can go and pretty this up a little bit. I can zoom out and maybe put this chart up here. Move this around. Maybe make the text even bigger. You know get it looking like I want.

So I hope through this example you learned a lot of basic Numbers skills. Thanks for watching.