Skip to main content

Posts

Showing posts with the label QUERY function

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

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