No. of Recommendations: 11
Some people build a portfolio consisting of three mutual funds and rebalance each year. This post is not for them.
Some people trade multiple times a day and live for technical immediacy and depend on broker-supplied real time charts. This post may, or may not, help them.
Most of us fall somewhere in between.
There are all sorts of both paid and free services which can assist in structuring a portfolio, but my concerns have always been about the security and/or use of the personal and financial information they require. That and the continuing subscription cost of many services – there are few free lunches.
Years ago, I decided to keep track of my portfolio, which, at times, has held nearly 100 stocks from a number of countries denominated in multiple currencies. So, that also meant it was valuable (to me, at least) to also track comparative currencies as well as precious metals.
The model was originally based on Microsoft Excel, using primarily data from Yahoo Finance. For technical reasons (they stopped functioning 😊), this has morphed to being Google Sheets oriented, largely using Google Finance, augmented by a number of other financial data sources.
The model was originally based on Microsoft Excel, using primarily data from Yahoo Finance. For technical reasons (they stopped functioning 😊), this has morphed to being Google Sheets oriented, largely using Google Finance, augmented by a number of other financial data sources.
While some of the functions may have to be corrected, there is a pretty good template found on:
http://www.tawcan.com It employs a fair degree of “scraping” (plucking required date from the midst of a table found on a targeted web page). I’ll try to explain the process, but be aware that if the owner of the web page changes its format, the function being used will have to be adjusted accordingly. As we are using their data for free, beggars can’t be choosers.
This post is intended to provide the tools required to easily customize a spreadsheet designed to provide real time tracking and reporting.
IMPORTANT NOTE: Copy/Paste back and forth can apparently screw up the quote character (making them curved instead of straight) causing syntax errors. Paste any formula which isn’t working into ChatGPT and ask it to correct the syntax. (I wasn’t aware of the issue and creating the below was a bit challenging – hope it just works “out of the box”).
The following functions may be helpful (Stock symbol in Column “B”):
Stocks:
Current quote:
=GoogleFinance(INDIRECT("B" & ROW()))
P/E (not all foreign stocks supported, no funds supported)
=GoogleFinance(INDIRECT("B" & ROW()),"PE")
The following will give the dividend and the yield (parse using MID text function):
For US stocks and ADR’s:
=INDEX(IMPORTHTML("
http://finviz.com/quote.ashx?t=" & INDIRECT("B" & ROW()), "table", 10), 8, 2)
For ETF’s:
=INDEX(IMPORTHTML("
http://finviz.com/quote.ashx?t=" & INDIRECT("B" & ROW()), "table", 10), 7, 2)
Distance from 20 Day simple moving average:
=INDEX(IMPORTHTML("
http://finviz.com/quote.ashx?t=" & INDIRECT("B" & ROW()), ”table", 10), 11, 8)
Distance from 50 Day simple moving average:
=INDEX(IMPORTHTML("
http://finviz.com/quote.ashx?t=" & INDIRECT("B" & ROW()), ”table", 10), 12, 8)
Distance from 200 Day simple moving average:
=INDEX(IMPORTHTML("
http://finviz.com/quote.ashx?t=" & INDIRECT("B" & ROW()), "table", 10), 13, 8)
Lots of other stock metrics at:
="
https://finviz.com/quote.ashx?t=" & INDIRECT("B" & ROW()) & "&ty=c&ta=1&p=d"
You can use the formats of the above to scrape additional metrics by using the row # and then the column number following the word “table” above
Currencies:
Currency pair ratios can be read as follows (example is Chinese to USA):
=GoogleFinance("Currency:CNYUSD")
The US dollar index is a bit more involved:
USDX = 50.14348112 × EURUSD^-0.576 × USDJPY^0.136 × GBPUSD^-0.119 × USDCAD^0.091 × USDSEK^0.042 × USDCHF^0.036
Precious metals:
Gold Spot:
=MID(INDEX(importhtml("
https://widget.nfusionsolutions.com/widget/table/5...","table",1),4,2),1,100)
Silver Spot:
=MID(INDEX(importhtml("
https://widget.nfusionsolutions.com/widget/table/5...","table",1),4,2),1,100)
Platinum Spot:
=MID(INDEX(importhtml("
https://widget.nfusionsolutions.com/widget/table/5...","table",1),4,2),1,100)
Hope this helps someone out there
Jeff
