Post New

I just posted the below to the Mechnical Investing board, but after reading Jim's comments about the drop in the dollar (and having ceased a while back talking about the dangers swift changes to the US dollar index - in either direction - would mathematically have to our "real" net worth, I decideed some here might find use for the functions outlined in the following modeling tools.

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.

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

I admit I do it in a much more primitive way.

To get my overall progress I take the current liquid investments balance (the main account reports in USD). I convert that to Euros and take the simple average of the two, what I think of as "mid Atlantic currency units" (MACU). Most big global currency moves are in one or the other, usually the USD, so a simple average gives you a not-bad idea of true progress of wealth / purchasing power over time.

For more precision:
I have all my current investment positions on a quote page in the IB trader workstation.
(For positions held at other brokerages, I still create a data line at that IB page, also a line for every forex rate of interest)
I export that data page using the "File | Import/Export | Export Page in Legacy Excel Format" as a temp .csv file to get all current quotes and current FX rates.
I open that and paste it into the Excel sheet where I have all my investment positions past and present at one per line, with a column for entry price.
Lookups into the pasted data give me the current prices and current FX for each, so the P/L for each is evident.
I copy the PL column at the end of each month, and freeze the values for the prior month, so I have a single big sheet with the PL (in one currency) of all positions at all month ends.

As mentioned, very primitive. But I can answer any question that interests me: how have I done overall over time, how has each position done, what did any position or the total look like since month X. It has resilience: I have the Excel sheet on my PC so I don't have to worry about the tracking being at the mercy of Google changing things.

The principal disadvantage is that it works so well, and has done so for so long, that I am running into the number of columns (months) that my ancient version of Excel supports. I've had to delete old columns so the older data is now quarterly instead of monthly.

Jim

I'm pretty parallel to what you have, with a few differences. While I invest in a number of currencies (for decades) and part of the strategy involves consciously diversifying from the USD, I admit to being myopic and filtering down to a USD bottom line in the same way that I can convert to Celsius temperature and "fake it" when talking to non-US people, I still think in Fahrenheit (I guess in the same way that, regardless of how well one speaks a foreign language, mathematics is usually done in your native one).

I have always been intimidated by IB's Trader Workstation. I feel like a pilot who has been trained on Piper Cubs taking the stick of an F35. I know what I am supposed to be able to do, but I always feel like, for some reason, I'm always trying to get the wrong screen to do the right thing.

That said, the Google Sheets spreadsheet I use to manage my equity portfolio is populated in native currencies with consolidated assets from the two brokers I use for stocks. The columns I use are fairly mundane:

Ticker Symbol
Name
Morningstar rating
ValueLine rating
Buy Date
P/E ratio
Div. Yield %
Total Cost Basis
Number of Shares
Cost/Share
Current (real time) Quote
% Day Change
$ Day Change
Current Value of position
% Profit/Loss
Total Profit
Proportion of total portfolio
Dividend/Share
Total Annual Dividend of position
Sector
Country
Broker Account
Currency

The spreadsheet is more or less self-maintain (both from a currency and a share standpoint), with the exception of new buys, sales, DRIPs and so on. It is complete with boxes decorously turning green/red, etc., a separate sheet with pretty graphs and so on - idle hands are the Devil's workshop - which I rarely pay much attention to.

I have a separate Excel worksheet (not on-line) which lists assets including brokerage accounts, fixed instruments, bank accounts, etc. - all funneling down to a bottom line compared to the previous year's (embarrassingly in USD). I have saved the end-of-year spreadsheet for decades - both to pat myself on the shoulder as well as providing a time capsule of accounts dates and detail, should I ever have to track some legacy action down.

Jeff

Post New

Berkshire Hathaway FAQ

---

Contact Shrewd'm
Contact the developer of these message boards.