Investment Leads

If you have surplus income and you live in a country where there are no high return safe investment options then investing in the market becomes one of the better choices. Given the plethora of information available on the internet regarding which stock or mutual fund to pick it is very easy to get overwhelmed. Luckily there is a way to capture all that information and create your own little dashboard of information using Google Sheets and Google finance API. Use it to verify leads, backtest strategies or just shortlist investment options, it’s up to you.

GoogleFinance

Google sheet comes with an inbuilt routine that can fetch real-time or historical for most stock symbols, mutual funds, ETFs and other investment vehicles. Signature for this routine is:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Ticker is the only mandatory element which should be prefixed by the market symbol, as such NASDAG:GOOGL

Equity data is divided into two categories, real-time and historical. With historical you get your usual OCHLV data from start to end date. Another gem sparkline can be used to plot miniature graphs of trends.

For real-time data there are a lot of fundamental analysis attributes that can be fetched for analysis, like P/E ratio, market cap etc. beside pricing and return data. Similarly mutual fund tickers get their specific information.

Detailed documentation can be found here.

Sample Sheet

I created this sample dashboard to shortlist assets for potential investment. Dashboard has three sections, short-term, long-term and mutual funds. First two section are stocks and contain following columns:

# C4: Market Symbol
# B4: Ticker

# Current Price
=GOOGLEFINANCE(CONCAT(C4, B4),"price")

# One Year Trend
= SPARKLINE(GOOGLEFINANCE(CONCAT(C4, B4),"high", TODAY()-356, TODAY()-1))

# One Month Trend
= SPARKLINE(GOOGLEFINANCE(CONCAT(C4, B4),"high", TODAY()-30, TODAY()-1))

# Market Cap
=ROUND(GOOGLEFINANCE(CONCAT(C4, B4),"marketcap")/1000000000, 2)

# Fundamentals
=JOIN(" : ", "PE " , GOOGLEFINANCE(CONCAT(C4, B4),"pe"), "Low52", GOOGLEFINANCE(CONCAT(C4, B4),"low52"), "High52", GOOGLEFINANCE(CONCAT(C4, B4),"high52"), "EPS" , GOOGLEFINANCE(CONCAT(C4, B4),"eps"))

Mutual funds have same number of columns but different information:

# NAV Price
=GOOGLEFINANCE(CONCAT(C18, B18),"closeyest")

# Expense Ratio
=GOOGLEFINANCE(CONCAT(C18, B18), "expenseratio")

# Morning Star Rating
=GOOGLEFINANCE(CONCAT(C18, B18), "morningstarrating")

# Net Assets Managed by Fund
=ROUND(GOOGLEFINANCE(CONCAT(C18, B18), "netassets")/1000000000, 2)

# Historical Returns
=JOIN(" : ", "year-to-date", GOOGLEFINANCE(CONCAT(C18, B18),"returnytd"), "52", GOOGLEFINANCE(CONCAT(C18, B18),"return52"), "156", GOOGLEFINANCE(CONCAT(C18, B18),"return156"), "260", GOOGLEFINANCE(CONCAT(C18, B18),"return260"))

Google sheet renders like this once done.

{{< googlesheetfin >}}