---

About

<table> <tr> <td>

• CACHEFINANCE is a custom function to supplement GOOGLEFINANCE.
  • Use this for ONE symbol and ONE attribute lookup.
• CACHEFINANCES is a custom function similar to CACHEFINANCE except it is used to process a range of symbols.
• Valid STOCK data is always available even when GOOGLEFINANCE refuses to work.
• Warning!!! When a stock/ETF switches to a new exchange and you do not update the exchange code, CACHEFINANCE will continue to report the LAST quote it was able to obtain for a very long period of time. This of course leads to portfolio valuations to drift from actual as time goes by. You therefore need to periodically manually inspect the CACHEFINANCE price versus a quote you would have with your broker.
• GOOGLEFINANCE does not support all stock symbols. Many unsupported google stocks can still get price/name/yield data (using web screen scraping).
• GOOGLEFINANCE does not support all currency conversions. CACHEFINANCE will lookup any failing currency conversions if GOOGLEFINANCE fails (strangely, I am pulling this data from the google finance web site - which works most of the time)
• As you can guess from the name, data is cached so when '#N/A' appears, it uses the last known value so that it does not mess up your asset history logging/graphing.
• All My Google Sheets Work
• CacheFinance Web site

</td> </tr> </table>

Installing

• Googles guide to adding custom functions: Google Help
• Copy files manually.
• In the ./dist folder there is one file. Only one is required.
  • CacheFinance.js
    • Caches GOOGLEFINANCE results AND does 3'rd party website lookups when all else fails.
      • This file is an amalgamation of the files in the /src folder.
      • Therefore do NOT use the files in /src folder.
• The simple approach is to copy and paste CacheFinance.js.
  • From your sheets Select Extensions and then Apps Script
  • Ensure that Editor is selected. It is the < >
  • Click the PLUS sign beside File and then select Script
  • Click on CacheFinance.js, and then click on Copy Raw Contents which puts the file into your copy buffer.
  • Back in your Google Project, rename Untitled to the file name you just selected in Github. It is not necessary to enter the .gs extension.
  • Remove the default contents of the file myFunction() and paste in the new content you have copied from Github (Ctrl-v).
  • Click the little diskette icon to save.
  • Change to your spreadsheet screen and try typing in any cell
  • =CACHEFINANCE() or =CACHEFINANCES(). The new function with online help should be available.

Using

• After adding the script, it will require new permissions.
• You need to open the script inside the Google Script editor, go to the Run menu and choose 'testYieldPct' from the dropdown. This will prompt you to authorize the script with the correct permissions.
• Fast REST API Websites
  • For faster stock price lookups when external finance data is used, add the key to Apps Script ==> Project Settings ==> Script Properties
  • The free API will have limitied functionality in all cases. They are all throttled and may only provide end of day pricing. You can always get a paid subscription and use that API key if your needs are greater.
    • Finnhub
      • Click on Edit Script Properties ==> Add Script Property.
        • Set the property name to: FINNHUB_API_KEY
        • Set the value to: 'YOUR FINNHUB API KEY'
          • Get your API key at: https://finnhub.io/
    • AlphaVantage
      • Click on Edit Script Properties ==> Add Script Property.
        • Set the property name to: ALPHA_VANTAGE_API_KEY
        • Set the value to: 'YOUR Alpha Vantage API Key'
          • Get your API key at: https://www.alphavantage.co/
    • TwelveData
      • Click on Edit Script Properties ==> Add Script Property.
        • Set the property name to: TWELVE_DATA_API_KEY
        • Set the value to: 'YOUR Twelve Data API Key'
          • Get your API key at: https://twelvedata.com/
    • CoinMarket
      • Site used for crypto currency lookups.
      • Click on Edit Script Properties ==> Add Script Property.
        • Set the property name to: COINMARKET_API_KEY
        • Set the value to: 'YOUR Coin Market API Key'
          • Get your API key at: https://coinmarketcap.com/api/
• Canadian Mutual Funds.
  • GOOGLEFINANCE does not support (the one I have anyway) Canadian mutual fund codes.
  • I use the Globe and Mail web site to extract the data.
    • Use the exchange code of 'CF' plus the mutual fund code like: "CF:DYN2752"
      • e.g. =CACHEFINANCE("CF:DYN2752", "PRICE")

Using as a custom function.

• The custom function CACHEFINANCE enhances the capabilities of GOOGLEFINANCE.
• When it is working, GOOGLEFINANCE() is much faster to retrieve stock data than calling a URL and scraping the finance data - so it is used as the default source of information.
• When GOOGLEFINANCE() works, the data is cached.
• When GOOGLEFINANCE() fails ('#N/A'), CACHEFINANCE() will search for a cached version of the data. It is better to return a reasonable value, rather than just fail. If your asset tracking scripts have just one bad data point, your total values will be invalid.
• Runnings custom functions (like CACHEFINANCE), can be slow. One approach to using the function is to ONLY have it run when GOOGLEFINANCE() fails and then run CACHEFINANCE.
  • e.g. =IFERROR(GOOGLEFINANCE("NEO:ZTL"),CACHEFINANCE("NEO:ZTL"))
• If the data cannot be found in cache, the function will attempt to find the data at various financial websites. This process however can take several seconds just to retrieve one data point.
• If this also fails, PRICE and YIELDPCT return 0, while NAME returns an empty string.
• CAVEAT EMPTOR. Custom functions are also far from perfect. If Google Sheets decides to throw up the dreaded 'Loading' error, you are almost back to where we started with an unreliable GOOGLEFINANCE() function.
  • However, in my testing it seems to happen more often when you are doing a large number of finance lookups.
• SYNTAX.
  • CACHEFINANCE(symbol, attribute, defaultValue)
  • symbol - stock symbol using regular GOOGLEFINANCE conventions.
  • attribute - three supported attributes doing 3'rd party website lookups:
    • "price"
    • "yieldpct"
    • "name"
    • You can specify other attributes that GOOGLEFINANCE uses, but the CacheFinance() function will not look up this data if GOOGLEFINANCE does not provide an initial default value.
    • This ATTRIBUTE name in this case is used to create our CACHE key, so its name is not important - other than when the function does a cache lookup using this key (which is made by attribute + "|" + symbol)
    • The following "low52" does not lookup 3'rd party website data, it will just save any value returned by GOOGLEFINANCE to cache, for the case when GOOGLEFINANCE fails to work:

      =CACHEFINANCE("TSE:ZIC","low52", GOOGLEFINANCE("TSE:ZIC", "low52"))
  

  • defaultValue - Use GOOGLEFINANCE() to supply this value either directly or using a CELL that contains the GOOGLEFINANCE value.

    • 'yieldpct' does not work for STOCKS and ETF's in GOOGLEFINANCE, so don't supply the third parameter when using that attribute.
    • Example: (symbol that is not recognized by GOOGLEFINANCE)
      • =CACHEFINANCE("TSE:ZTL", "price", GOOGLEFINANCE("TSE:ZTL", "price"))

  • Special (Back Door) Commands

    • "TEST" - special case. Lists in a table results of a sanity test to third party finance sites.
      • This will generate about twenty rows of output, so it is necessary to make sure that there is no cells with data (otherwise it fails to run).
        • =CACHEFINANCE("", "", "TEST")
    • "CLEARCACHE" - special case