free stock portfolio tracking spreadsheet

In this example, it is the $790.21 cost basis of ULTA divided by the $50,727.92 total cost basis of the portfolio. The cost basis of the position represented as a percentage of the whole portfolio, automatically calculated. 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. If it’s a stock we don’t own yet, we enter a zero. The number of shares we currently own of the stock. We enter the symbol of the stocks we either own or are looking to add in this column. So if we want to use the GoogleFinance function to display one of these attributes we use the following formula in the spreadsheet cell:įor example, if we want to display the current P/E ratio for Apple, we use this formula in the spreadsheet cell:Įasy enough, right? The spreadsheet in detailĪcross the top of the spreadsheet there are 14 column headers. currency: the currency in which this stock is traded.shares: the number of shares outstanding of this stock.closeyest: yesterday’s closing price of this stock.changepct: the percentage change in the price of this stock since yesterday’s close.

change: the change in the price of this stock since yesterday’s market close.

high52: the 52-week high for this stock.

eps: the earnings-per-share for this stock.

pe: the Price-to-Earnings ratio for this stock.

volumeavg: the average volume for this stock.

datadelay: the delay in the data presented for this stock using the googleFinance() function.

tradetime: the last time the stock traded.

marketcap: the market cap of the stock.

volume: number of shares traded of this stock for the current day.

low: the lowest price the stock traded for the current day.

high: the highest price the stock traded for the current day.

priceopen: the opening price of the stock for the current day.

price: market price of the stock – delayed by up to 20 minutes.

According to the help page, the GoogleFinance function will let us pull in these attributes for an equity: Google Finance functionsįirst, let’s cover what financial data Google Sheets can pull in. This helps me identify which stocks in my portfolio are reaching a trigger point where I may want to buy, which I typically base on P/E ratio.

I chose Google Sheets because it can query Google Finance to pull market data. If you just want to download a copy scroll to the bottom of this page. I use a Google Sheet to track my stock portfolio. This is an example of what the spreadsheet looks like.

Free stock portfolio tracking spreadsheet