Skip to main content

Posts

Showing posts from September, 2022

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