Tracking your investment portofilio is key to answering the question ‘am I beating the market?’
Google provides the Google Finance function in Google Sheets to track stock performance. It makes portfolio tracking easy, fast and accurate. Let’s see how.
First, track a stock you are interested in (Amazon, in the example below).
=GOOGLEFINANCE("AMZN","close", DATE(2020, 6, 6))Historical data comes in two columns, so to get just the stock value, you need to use index formula:
=INDEX(GOOGLEFINANCE("AMZN","close", DATE(2020, 6, 6)),2,2)Then, you can also apply a currency conversion:
=INDEX(GOOGLEFINANCE("AMZN","close", DATE(2020,6,26)),2,2)*
INDEX(GOOGLEFINANCE("CURRENCY:USDGBP", "price", DATE(2020,6,6)),2,2)Finally, you can multiply the stock price adjusted for currency by your holding (how many shares you own) to get a value.
=INDEX(GOOGLEFINANCE("AMZN","close", DATE(2020,6,26)),2,2)*
INDEX(GOOGLEFINANCE("CURRENCY:USDGBP", "price", DATE(2020,6,6)),2,2)
* 3Additionally, if you’d like to track values on a monthly basis, you can use the end of month function to get the suitable dates.
Together, these steps allow you to easily track your holdings over time.
View my Amazon example to see the formulas in action yourself. You can also see the full list of stocks available via the function.