Financial Portfolio Tracker

In previous blog Planning Financial Health I have explained how my InvestingNTracking sheet helps me in tracking and deciding investment decisions. In this blog, I will explain how I went about building it.

GOALS

Following were my goals to build such a sheet:

1. Help me decide where to invest
   • Show NSE India stock’s price, trends, dividents
   • Show trendline
2. Helps me track my investment portfolio
   • Calculate overall profit/loss for each of my investment
   • Compare my various investments for rebalancing
3. Be flexible enough to be operable from phone or while travelling

DESIGNING SHEETS

Since I was doing a sheet, I would automatically satisfy goal #3 as stated above. I had two clear purposes to look for:

• Show portfolio performance and help channelize new investments
• Ability to add information about any new investment made

I had thence split the workbook into two worksheets, one where I would just add my transactions and another which is a dashboard for each stock performance. I can carry forward the average price of a stock unit and the number of shares bought into my first sheet for a fresh start. This could have been done by Google Apps Script, but currently, I left that as a manual process albeit after doing some automation with AVERAGEIF / SUMIF

So, my manually entered fields are:

No_Units=SUMIF(cell_range_with_ticker, "=" & cell_ref_with_ticker, cell_range_with_transaction_units)
Avg_Unit_Price=AVERAGEIF(cell_range_with_ticker, "=" & cell_ref_with_ticker, cell_range_with_transaction_unit_price)

USING GOOGLE FINANCE APIs

Google Finance APIs helps pull all information related to a stock, such as Last Traded Price (LTP), High, Lows, Profit-Earning ratio, Earnings per Share, etc for FREE.

// prints respective attribute as mentioned
PRICE=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"PRICE")
HIGH52=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"HIGH52")
LOW52=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"LOW52")
HIGH=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"HIGH")
LOW=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"LOW")
CLOSEYEST=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"CLOSEYEST")
CHANGEPCT=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"CHANGEPCT")
VOLUME=GOOGLEFINANCE("NSE:" & cell_ref_with_ticker,"VOLUME")

// prints a trend graph in the cell
=SPARKLINE(GOOGLEFINANCE(cell_ref_with_ticker, "PRICE", TODAY()-179, TODAY()))
=SPARKLINE(GOOGLEFINANCE(cell_ref_with_ticker, "PRICE", TODAY()-365, TODAY()))

SCRAPING YAHOO FINANCE

I had pulled most of the information as needed except the dividend, for which I had to use Yahoo Finance since it shows dividend information in the first page. I initially tried doing similarly with Google Finance, but it was inconsistent - feel free to validate this part on your own.

// prints divident percentage of a stock
DIVIDENT=index(split(IMPORTXML(concatenate("http://finance.yahoo.com/quote/",cell_ref_with_ticker,".NS"),"//*[@id=""quote-summary""]/div[2]/table/tbody/tr[6]/td[2]/text()"),"(%)"),0,2)

HACKS

In order to add my cash, I added it as a separate row, froze it to top of the sheet and mentioned stock code as INR_cash, with a dividend of 3% since that is the saving bank interest rate in India.

I used the following formulae to calculate profit and loss of the whole portfolio;

Total_Buy_Price = No_of_Units * Avg_Unit_Price
Total_Price = Sum of Total_Buy_Price for all investments
Purchase_Weight = Total_Buy_Price / Total_Price
Yield = PRICE * DIVIDENT * No_of_Units
Price_Profit = (PRICE - Avg_Unit_Price) * No_of_Units
Total_Profit = Yield + Price_Profit