Today we’re going to geek out in a spreadsheet! I was talking with some friends over at The Motley Fool and mentioned to them that I use a Google spreadsheet to track my stock portfolio.
Here’s an example of what the spreadsheet looks like, scroll to the bottom to download a copy:
I chose to use a Google spreadsheet because it can query Google Finance and pull in live market data, which is awesome. This helps me easily identify which stocks in my portfolio are reaching the trigger point where I want to buy them, which is typically based on the PE ratio.
Google Finance Functions
First, let’s cover what financial data a Google spreadsheet can pull. According to this help page, the GoogleFinance function will let us pull in these attributes for an equity:
- price: market price of the stock – delayed by up to 20 minutes.
- priceopen: the opening price of the stock for the current day.
- high: the highest price the stock traded for the current day.
- low: the lowest price the stock traded for the current day.
- volume: number of shares traded of this stock for the current day.
- marketcap: the market cap of the stock.
- tradetime: the last time the stock traded.
- datadelay: the delay in the data presented for this stock using the googleFinance() function.
- volumeavg: the average volume for this stock.
- pe: the Price-to-Earnings ratio for this stock.
- eps: the earnings-per-share for this stock.
- high52: the 52-week high for this stock.
- low52: the 52-week low for this stock.
- change: the change in the price of this stock since yesterday’s market close.
- beta: the beta value of this stock.
- changepct: the percentage change in the price of this stock since yesterday’s close.
- closeyest: yesterday’s closing price of this stock.
- shares: the number of shares outstanding of this stock.
- currency: the currency in which this stock is traded.
If we want to use the GoogleFinance function to display one of these attributes, we use the following formula in the spreadsheet cell:
For example, if we want to display the current PE ratio for Apple, we use this formula in the spreadsheet cell:
Easy enough, right?
The Spreadsheet in Detail
Across the top of the spreadsheet there are 14 column headers. Let’s run through these one-by-one.
Column A – Stock
We enter the symbol of the stocks we either own or are looking to add in this column.
Column B – Shares
The number of shares we currently own of the stock. If it’s a stock we don’t own yet, we enter a zero.
Column C – Cost Basis
The cost basis of the shares we own. After I purchase a new position or I sell shares, I update this cell with the cost basis provided to me in my brokerage account.
Column D – % Cost
The cost basis of the position represented as a percentage of the whole portfolio, automatically calculated.
In this example, it is the $790.21 cost basis of ULTA divided by the $50,727.92 total cost basis of the portfolio. This helps me gauge if the stock is doing the heavy lifting or if I am by adding cash to the position.
Column E – Value
The real time value of the position. In this example, the number of ULTA shares in Column B, 10, is multiplied by the real time share price in Column J, $77.22.
Column F – % Port
This value is the percentage the position represents as part of the portfolio. In this example, the value of the position in Column E, $772.20, is divided by the $88,887.78 total value of the portfolio.
Column G – % Goal
This is the percentage of the portfolio we want to target for the position. I personally set targets at 0.5%, 1%, 3%, 5%, and 7%.
Column H – % Delta
The percentage we want the position to be less the current percentage of the position determines the percentage delta.
In this example, we want ULTA to be 7.00% of our portfolio (the target goal in Column G) and subtract the current percentage of the position in Column F, 0.87%.
This value is automatically calculated and will fluctuate based on the whole portfolio.
Column I – $ Delta
Based on the percentage of our portfolio we want to allocate towards the stock, this is the real time dollar amount our position is overweight/underweight.
It’s automatically calculated by taking the delta percentage in Column H (in this example -6.13%) multiplied by the $88,887.78 total value of the portfolio. Therefore, if we want a full position in ULTA right now, we need to purchase an additional $5,449.94 worth of shares.
I like to highlight the spreadsheet cells in red whenever this dollar amount exceeds a threshold in order to highlight positions where cash needs to be added.
Column J – Current Price
The current share price, it can be delayed by up to 20 minutes although I find it to be real-time. To get this data we use the ticker symbol in Column A within the cell like this:
Or we can use the ticker symbol directly in the cell like this:
Column K – Target Price
The real-time share price we’re targeting to add shares. For me this is usually based on the PE ratio so the function in this cell is:
=GoogleFinance("ULTA"; "EPS") * M22
This tells Google Finance to query the current EPS of ULTA and then the spreadsheet takes that value, multiplies it by my target PE of 28 in Column M, and then displays the share price where I should target shares.
I also find it useful to have the current price in a column next to the target price, so we can do a quick scan to know where the prices are coming inline.
Column L – Current PE
The current PE ratio. The ticker symbol in Column A can be used in the cell like this:
Or we can use the ticker symbol directly in the cell like this:
Column M – Target PE
We need to fill this cell in with the target PE where we want to add more shares. In this example I am targeting a PE of 28 to add more shares of ULTA.
This value is used in Column K to calculate the accompanying share price. I also find it useful to have the current PE in a column next to the target PE, so we can do a quick scan to know when the PE’s are coming inline.
Column N – Last PE
Completely optional, but I use this column to track the PE when I last purchased shares. One of my goals is to add shares at better value points (lower PE’s), so it’s a quick reference.
Maintenance and Mistake Proofing
The only maintenance required for the spreadsheet is to:
1. Update the cost basis and number of shares when positions are added or sold.
2. Update the Cash value in Column A when new cash is added to the portfolio or positions are bought/sold.
The totals across the bottom of the spreadsheet ensure there are no mistakes in the spreadsheet.
Download the Spreadsheet, Free!
Enter your information below. You’ll then receive an email, click the link and you’ll get the spreadsheet!