Linking Open Source API to Google Spreadsheets to Track Investment: GOOGLEFINANCE, IEX and Alpha Vantage

What is API

API stands for Application Programming Interface. According to Google, API is a set of functions and procedures that allow the creation of applications which access the features or data of an operating system, application, or other service. In simple terms, it means a specification of remote calls for a program to interact with another software. API calls are required to retrieve data from the sources, such that you do not have to manually key in the data from the source to update your investment tracker. The data is being refreshed via the feed, freeing up your time for analysis instead of doing data entry.

In this article, I will share the basics of setting up simple API calls from GoogleFinance, IEX and Alpha Vantage.

Credits to fellow bloggers for your tips and tricks – twoinvesting and $$$KPO and CZM$$$.

Why link to Google Sheets

Reason for my preference for Google Sheets is because the file is stored on cloud, meaning I can access it anywhere I want to. If you are a seasoned Excel user, you may find Google Sheets inconvenient to use as their user interface is slightly different and some functions are not available.

Another reason will be the GOOGLEFINANCE function that I use. As a tech noob person, I prefer simplicity to writing the codes so that I can spend more time doing analysis (which is what I prefer) instead of figuring out bugs and editing the script. (if you’re into coding and would like to showcase/ share your talent, please beep me and I would be glad to speak with you)

GOOGLEFINANCE API calls

No set up for this is required, hence it is easy to use. However, the variety of data set and stock exchange data available are limited which calls for the other open source API’s to complement GOOGLEFINANCE. For my case, data from the Singapore exchange (SGX) is unavailable.

Syntax will be:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])where ticker = ticker symbol for the security

[ ] = optional attributes; “price” by default

For full list of attributes available on GOOGLEFINANCE, please refer to link.

Currency exchange rate – GOOGLEFINANCE(“CURRENCY:USDSGD”)

IEX API

Create an account on IEX website for an API key. Keep this unique key safe as you will be using it in the calls. Documentation can be found here.

Steps:
1. Register for API Key
2. Create Google Sheets IMPORTJSON Function using Google Script
3. Create API calls from IEX

Syntax will be:

https://api.iextrading.com/1.0/stock/aapl/price

Alpha Vantage API

Alpha Vantage Free API- Personal Portfolio

Steps:
1. Register for API Key
2. Create Google Sheets IMPORTJSON Function (refer to video on steps) using Google Script Editor
3. Create API calls from Alpha Vantage

Syntax will be:

https://www.alphavantage.co/query?function=RSI&symbol=AXP&interval=weekly&time_period=10&series_type=open&apikey=YOUR_API_KEY