X

Send us a Topic or Tip

Have a suggestion for the blog? Perhaps a topic you'd like us to write about? If so, we'd love to hear from you! Fancy yourself a writer and have a tech tip, handy computer trick, or "how to" to share? Let us know what you'd like to contribute!

Thanks for reaching out!

Tracking Stock Values in Numbers

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!


tracking stock values
The Apple Stocks app

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:

tracking stock values numbers app
A sample investment spreadsheet in Numbers

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:

selecting stock ticker symbol
Searching for Canon’s ticker symbol in Stocks

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):

STOCK function in Numbers
The STOCK function in Numbers

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:

formula closing share price for tracking stock values
The formula for finding the closing share price for the Canon stock ticker symbol in cell C13

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”:

formula for tracking stock values
The formula for calculating investment value

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:

tracking stock values total investment in numbers
The SUM function used to total all investments

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.


stock values
Photo by Adeolu Eletu on Unsplash

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.

Steve Sande
the authorSteve Sande
Contributing Author
Steve has been writing about Apple products since 1986, starting on a bulletin board system, creating the first of his many Apple-related websites in 1994, joining the staff of The Unofficial Apple Weblog in 2008, and founding Apple World Today in 2015. He’s semi-retired, loves to camp and take photos, and is an FAA-licensed drone pilot.
Be Sociable, Share This Post!

Leave a Reply

Comment

Name

2 Comments

  • 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.