Skip to main content

Posts

Showing posts with the label SLICE

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

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