Skip to main content

Posts

Showing posts with the label Google Sheets

GOOGLEFINANCE Best Practices

Anyone using Google Sheets to manage stock portfolio investment must know how to use the GOOGLEFINANCE function to fetch historical prices of stocks. As I have used it extensively to manage my stock portfolio investment in Google Sheets , I have learned several best practices for using the GOOGLEFINANCE function that I would like to share in this post. Some inconveniences of using the GOOGLEFINANCE function to fetch historical prices Include today price in the historical prices returned by the GOOGLEFINANCE function Ignore the Date column returned by the GOOGLEFINANCE function Ignore the headers row returned by the GOOGLEFINANCE function Keep only the price Create a dedicated sheet to store prices for each stock to limit calls to GOOGLEFINANCE function Use QUERY function instead of VLOOKUP function for looking up by date Conclusion Some inconveniences of using the GOOGLEFINANCE function to fetch historical prices In Google Sheets, the GOOGLEFINANCE function allows fetchi

Use SPARKLINE to create 52-week range price indicator chart for stocks in Google Sheets

The 52-week range price indicator chart shows the relative position of the current price compared to the 52-week low and the 52-week high price. It visualizes whether the current price is closer to the 52-week low or the 52-week high price. In this post, I explain how to create a 52-week range price indicator chart for stocks by using the SPARKLINE function and the GOOGLEFINANCE function in Google Sheets. Concept Demo Conclusion Concept With the GOOGLEFINANCE function, it's possible to retrieve the current price, the 52-week low price, and the 52-week high price of a stock by using the below formulas: =GOOGLEFINANCE("AAPL") returns the latest price of APPLE stock =GOOGLEFINANCE("AAPL","low52") returns the 52-week low price of APPLE stock =GOOGLEFINANCE("AAPL","high52") returns the 52-week high price of APPLE stock To measure the relative position of the current price compared to the 52-week low price and 52-wee

Slice array in Google Sheets

Many functions in Google Sheets return an array as the result. However, I find that there is a lack of built-in support functions in Google Sheets when working with an array. For example, the GOOGLEFINANCE function can return the historical prices of a stock as a table of two columns and the first-row being headers Date and Close. How can I ignore the headers or remove the headers from the results? Make any JavaScript method available in Google Sheets Demo and source code References Make any JavaScript method available in Google Sheets In JavaScript, there is the SLICE method that can return a part of an array. If I have an array const pricesWithHeader = ['Close', 10.5, 10.3, 10.1, 10.0]; , to get only the last 4 elements [10.5, 10.3, 10.1, 10.0] , I can apply the SLICE method like const pricesWithoutHeader = pricesWithHeader.slice(1); . How to slice an array in Google Sheets? Google Sheets has scripting capability with Apps Script based on JavaScript. So to slice

Compute cost basis of stocks with FIFO method in Google Sheets

After selling a portion of my holdings in a stock, the cost basis for the remain shares of that stock in my portfolio is not simply the sum of all transactions. When selling, I need to decide which shares I want to sell. One of the most common accounting methods is FIFO (first in, first out), meaning that the shares I bought earliest will be the shares I sell first. As you might already know, I use Google Sheets extensively to manage my stock portfolio investment, but, at the moment of writing this post, I find that Google Sheets does not provide a built-in formula for FIFO. Luckily, with lots of effort, I succeeded in building my own FIFO solution in Google Sheets, and I want to share it on this blog. In this post, I explain how to implement FIFO method in Google Sheets to compute cost basis in stocks investing. FIFO example How to do FIFO in Google Sheets How to use FIFO formula in Google Sheets Simple usage Use FIFO with QUERY formula Demo Conclusion FIFO exampl

How to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio with Google Sheets

As a long-term investor, I need to know how to evaluate the performance of my stock portfolio. A simple return on investment calculation is not a good indicator for long-term investment because it does not take into account the holding duration, and cash flows involved during that period. A return on investment of 80% after 20 years is not as impressive as it sounds after 1 year. In this post, I explain the idea of using Google Sheets to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio. Identify cash flows Choose a discount rate Apply XIRR and XNPV functions of Google Sheets Interpret internal rate of return (IRR) and net present value (NPV) Conclusion Series: how to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio in Google Sheets

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existen