Investment Portfolio Tracker
We all invest in stocks and Mutual funds and tracking their performance sometimes becomes a problem.
So I found this wonderful tracker by Kyith on http://www.investmentmoats.com/ which is an excellent tool to keep track of your investments. Kyith has made a google spreadsheet for tracking the stocks. I have just converted that google spreadsheet into excel spreadsheet and added some functionalities and also removed certain features like cash register, dividend tracker (Which I didn’t required).
Features of this Tracker
1. Calculates the cost on weighted average basis
2. Gives transaction wise profit & loss
3. Consolidated view of current holdings
How to use this tracker
1. The spreadsheet fetches the prices of stock from MSN Money India and for mutual funds AMFI India.
To fetch the price of stocks from MSN Money US, just change the highlighted part below to “en-us” in the query.
http://www.msn.com/en-in/money/quoteslookup?SYMBOL=[“QUOTE”,”Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas.”]
2. You have to enter data only in sheets “Transactions” and “Stock Summary”
3. Enter your transactions in the sheet labelled “Transactions”. Enter data into columns highlighted in light yellow
4. In sheet “Stock Summary” fill columns highlighted in light yellow. You can get the stock symbols on MSN Money website.
Notes:
1. The AMFI query does not get auto refreshed, so you have to manually refresh the query. To manually refresh go to Data > Refresh All.
2. “AMFI Pivot” and “Current Holding Summary” sheet also needs to be manually refreshed. To refresh these pivot tables click anywhere on the table then go to Analyze > Refresh
You can download the Portfolio excel tracker here.
If you prefer the Google spreadsheet version by Kyith you can find it here. Also Kyith has written a detailed article on how to use his spreadsheet, which you can find on above link.
All credits go to Kyith for this spreadsheet.
UPDATE 1 – 11 AUG 2017
Somewhere in mid-July 17, Microsoft stopped supporting downloading stock quotes in Excel from MSN Money using ‘MSN MoneyCentral IQY Web Query’.
Now, to download the stock quotes in Excel, you can use the ‘WEBSERVICE’ function with Yahoo Finance or Google finance.
Yahoo Finance
- To download the quotes using Yahoo finance use the following formula:
=WEBSERVICE(“https://download.finance.yahoo.com/d/quotes.csv?s=Enter_Stock_Symbol&f=l1″) - You can get the stock symbol from Yahoo finance.
- Yahoo finance can get the stock quotes for companies listed on US stock exchanges
Google Finance
- To download the quotes using Google finance use the following formula:
=WEBSERVICE(“http://finance.google.com/finance/info?client=ig&q=Stock_Exchane_Symbol:Stock_Symbol“) - For example you want to download quote for Microsoft from NASDAQ stock exchange, the symbol would look like this NASDAQ:MSFT
- You can download stock quotes from all the major stock exchanges in the world, through Google finance
I have updated the portfolio tracker to download stock quotes from Google Finance.
UPDATE 2 – 09 SEPT 2017
The Google finance URL is not working any more, so you can use Yahoo Finance. In my experience, you can download quotes only from a handful of stock exchanges located in US & UK through Yahoo Finance. To get quotes from stock exchanges which are not supported by Yahoo Finance, you can use Google finance to get prices in a Google spreadsheet and link that spreadsheet to Excel tracker.
Getting prices in Google spreadsheet from Google finance
- Open a new spreadsheet in Google Docs. In column A write the name of company, in column B write the symbol of that company and in column C use this formula to get stock price;
=GOOGLEFINANCE(“stock_symbol”,”price”)
You can get more information on googlefinance function on their help page. - After setting up the spreadsheet. Publish it, so that it can be connected to Excel.
To publish the spreadsheet go to File > Publish to web. select web page as publishing option (selected by default). And copy the publishing link generated. - Now in the Excel tracker in a new worksheet go to Data tab. And click get external data From Web. Now paste the link copied earlier and select the table to import stock quotes in excel.
- Link the stock quotes imported from Google spreadsheet in “stock summary” sheet using “VLOOKUP” or “INDEX & MATCH” or any other method convenient to you.
- All done and you are good to track performance your investments automatically.