{"id":81467,"date":"2022-04-05T09:00:00","date_gmt":"2022-04-05T14:00:00","guid":{"rendered":"https:\/\/eshop.macsales.com\/blog\/?p=81467"},"modified":"2022-04-04T16:07:25","modified_gmt":"2022-04-04T21:07:25","slug":"tracking-stock-values-in-numbers","status":"publish","type":"post","link":"https:\/\/eshop.macsales.com\/blog\/81467-tracking-stock-values-in-numbers\/","title":{"rendered":"Tracking Stock Values in Numbers"},"content":{"rendered":"\n

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<\/strong> 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!<\/p>\n\n\n\n

\n\n\n\n
\"tracking
The Apple Stocks app<\/figcaption><\/figure>\n\n\n\n

Why not use the Stocks app?<\/h2>\n\n\n\n

The Apple Stocks<\/strong> 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.<\/p>\n\n\n\n

\n\n\n\n

Creating a Numbers spreadsheet for tracking stock values<\/h2>\n\n\n\n

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:<\/p>\n\n\n\n

\"tracking
A sample investment spreadsheet in Numbers<\/figcaption><\/figure>\n\n\n\n

The first column shows the investments our hypothetical investor has, with the second column showing how many shares are owned. <\/p>\n\n\n\n

Find the ticker symbol<\/h3>\n\n\n\n

The ticker<\/strong> symbol<\/strong> 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<\/strong>, Tesla is TSLA<\/strong>, and many investment funds have their own symbols like FXAIX<\/strong>. Bitcoin is BTC-USD<\/strong>. Even commodities can show up here — orange juice futures show up as OJ=F<\/strong>. <\/p>\n\n\n\n

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:<\/p>\n\n\n\n

\"selecting
Searching for Canon’s ticker symbol in Stocks<\/figcaption><\/figure>\n\n\n\n

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<\/strong>.<\/p>\n\n\n\n

Once you have the ticker symbol, place it in one of the rows of your spreadsheet.<\/p>\n\n\n\n

The STOCK function for tracking stock values<\/h3>\n\n\n\n

The key to this process is a Numbers function called STOCK<\/code> 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<\/code>, STOCKH<\/code> (which retrieves historical prices) and CURRENCY<\/code> (which grabs currency exchange rates):<\/p>\n\n\n\n

\"STOCK
The STOCK function in Numbers<\/figcaption><\/figure>\n\n\n\n

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(<\/code> then click on the ticker symbol:<\/p>\n\n\n\n

\"formula
The formula for finding the closing share price for the Canon stock ticker symbol in cell C13<\/figcaption><\/figure>\n\n\n\n

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. <\/p>\n\n\n\n

Calculate total investment value<\/h3>\n\n\n\n

The formula for the column we’ve named “Value” is quite simple. It’s the number of shares multiplied by “yesterday’s closing price”:<\/p>\n\n\n\n

\"formula
The formula for calculating investment value<\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

To calculate the total portfolio value, just sum the column of values:<\/p>\n\n\n\n

\"tracking
The SUM function used to total all investments<\/figcaption><\/figure>\n\n\n\n

To add this formula, click in the cell (in this case E11), type =SUM<\/code>, drag over the sum of the cells, click the green checkmark button, or press enter. <\/p>\n\n\n\n

\n\n\n\n
\"stock
Photo by\u00a0Adeolu Eletu<\/a>\u00a0on\u00a0Unsplash<\/a><\/figcaption><\/figure>\n\n\n\n

Have fun tracking stock values in Numbers!<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

There’s more you could do with a spreadsheet like this. For example, use the STOCKH<\/code> 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.<\/p>\n\n\n\n