If you have a portfolio of investments, you know that it can be difficult to keep up with the current value. You’re probably familiar with the Numbers app (macOS, iOS, iPadOS). With Numbers, it’s possible to create a portfolio of stocks, market funds, cryptocurrencies, commodities, and more. The value of the portfolio updates automatically every day. Here’s how to start tracking stock values in Numbers!
Why not use the Stocks app?
The Apple Stocks app for iOS, iPadOS, and macOS has a lot of nice features, including real-time price tracking and the ability to make a “watch list” of your favorite stocks, indexes, and so on. On the other hand, there’s no way within the app to see the value of your current overall portfolio. That’s where the Numbers app and a special function come in handy.
Creating a Numbers spreadsheet for tracking stock values
To begin with, start by creating a spreadsheet in Numbers with your various investments listed in rows, as well as the current number of shares you may hold. The following screenshot shows an example:
The first column shows the investments our hypothetical investor has, with the second column showing how many shares are owned.
Find the ticker symbol
The ticker symbol used for the investment is very important, as it is used in our calculation. Think of the symbol as a shorthand code for a company’s stock or another investment instrument like Bitcoin. Apple is AAPL, Tesla is TSLA, and many investment funds have their own symbols like FXAIX. Bitcoin is BTC-USD. Even commodities can show up here — orange juice futures show up as OJ=F.
The Stocks app is quite useful in finding ticker symbols. Launch it and type in a company, fund, commodity, or other investment instrument name, and you’ll likely find the symbol:
In this image, we’re searching for Canon, the camera and technology company. You’ll need to know which exchange you’re dealing with. In this case, we’ll assume that we’re trading on the New York Stock Exchange (NYSE) in US dollars and not on the Tokyo Stock Exchange in Japanese Yen. The symbol is CAJ.
Once you have the ticker symbol, place it in one of the rows of your spreadsheet.
The STOCK function for tracking stock values
The key to this process is a Numbers function called STOCK that retrieves the stock price at the previous day’s close. To enter that function into the cell that retrieves the price, type an equals sign to bring up the formula editor, then start typing STOCK. There are actually several functions that the Functions browser (at right below) displays — STOCK, STOCKH (which retrieves historical prices) and CURRENCY (which grabs currency exchange rates):
To get the closing price from the previous day’s trading, we need to type an open parentheses “(” and click on the ticker symbol cell. For example, to get the Canon share price, we’d type =STOCK( then click on the ticker symbol:
Now just click the green checkmark button or press Return to finish the formula and retrieve the value. Note that like any formula in a cell in Numbers, this can be copied and pasted into a number of rows.
Calculate total investment value
The formula for the column we’ve named “Value” is quite simple. It’s the number of shares multiplied by “yesterday’s closing price”:
In this case, we’re looking at the formula in cell E2 on the spreadsheet. Once entered, this too can be copied and pasted into a number of rows.
To calculate the total portfolio value, just sum the column of values:
To add this formula, click in the cell (in this case E11), type =SUM, drag over the sum of the cells, click the green checkmark button, or press enter.
Have fun tracking stock values in Numbers!
Whether you’re just starting to make investments or have an impressive portfolio, tracking stock values in Numbers automates what could be an onerous manual process. Each day when you open this spreadsheet, it is automatically updated with the previous day’s share prices, and your investment values are calculated.
There’s more you could do with a spreadsheet like this. For example, use the STOCKH function to calculate your investment’s value on the day you initially purchased the stock and see how much the investment has gained or lost over time.
Related Topics
How do you add stock prices in Numbers for companies listed outside the US?
Hi Steve,
Regarding; “On the other hand, there’s no way within the app to see the value of your current overall portfolio. That’s where the Numbers app and a special function come in handy.”
I’m not sure if I’m missing something here, but isn’t the Market Value on Apple’s Numbers app giving you the Value of your portfolio?
Thanks for the information. I went to Numbers to try it out and found Apple includes a “My Stocks” template that appears to do everything (most?) you talk about plus more. It also includes Cost Basis, which for me would be difficult to determine since almost all of my retirement is in mutual funds with dividends being reinvested. Whichever version people use would be a major improvement to tracking changes by hand.