What caught my eye this week.
Long-time readers may recall that my investing life is ruled by an enormous multi-sheeted monster of a Google Spreadsheet.
This spreadsheet doesn’t just track my actively-managed portfolio and its returns.
It also keeps tabs on everything from my private investments to the cashflows that enable me to unitise my returns and so keep score versus the professionals.
My spreadsheet also surfaces interesting data and charts, such as where I’m geographically exposed and what proportion of my wealth is tax-sheltered and how.
There’s even an implied sustainable withdrawal rate sheet with various scenarios.
Needless to say, this spreadsheet also tells me my up-to-the-second net worth. (For good or ill).
Naturally this value includes the asset that is my house, as well as the liability of my mortgage.
But because I have various windows onto my financial status, I can see figures with or without the house/mortgage. (And the same for my pension).
Such distinctions help because, for instance, your personal home is an extremely important asset that should be factored in when you consider your net worth and financial posture – but for most of us it can crowd out insights into how your ‘true investing’ portfolio is structured.
For example, if you have 5% in a REIT and 45% of your net worth in your home, then you have 50% property exposure. Sometimes it’s useful to think that way. More often not so much.
I even have a ‘liquid/illiquid’ view that groups my pension (for now) and my private investments together as being functionally inaccessible.
This is handy to see what I could get at as cash if I had to cut and run. (The hopefully very unlikely Plan B aka bugout scenario…)
I did it my way
Some masochistic readers have asked me to share this sheet – or at least the template – as we do our mortgage repayment-or-invest calculator.
But I’m not sure that’s the best idea. Mostly because I created this sheet to fit my personality and goals over the years.
I’m weird, and yours will be different.
Case in point: when I open it up, the first two sheets of my spreadsheet are watchlists – a fairly futile attempt to stop me focussing on the noisy movements of what I do own, and instead pay attention to the potential of what I don’t own.
At times I’ve even buried all my return data in a separate sheet at the end of a long line others, though that’s not my current set-up.
Again: an attempt to force better behaviour through structure.
Creating and evolving your own spreadsheet like this will teach you things about how you invest, too. And that’s valuable in itself.
They did it their way
Perhaps I will post more about my sheet someday.
But in the meantime check out Nick Maguilli’s article this week on how to track your net worth at Of Dollars and Data.
Nick’s shared his own net worth-tracking spreadsheet, too.
Its vastly simpler than mine, and probably better-suited to the majority of our sensible passive investing readers. You can always use it as a starting point if you want to build something more complicated.
One snag is last time I looked live UK fund data is a lot harder to come by with the Google query functions Nick employs for his ETF-based portfolio.
I have less than 1% in non-listed funds at present and I just update the values manually when I think it matters. But from memory you can scrape fund price data from Yahoo Finance with alternative calls to bring live values into Google.
A few years ago Fire V London shared a spreadsheet that pulled fund data from Hargreaves Lansdown, so that’s worth checking out too.
A Google search reveals this is a common roadblock. If anyone has the current and definitive solution, please do share it in the comments below.
Finally, if you’re just looking for an off-the-shelf tracker, then here’s how The Accumulator does it with Morningstar. Note that TA prefers money-weighted returns to unitisation.
What better project for the long weekend than overhauling your portfolio tracking? (I’m not entirely joking, given the weather…)
Enjoy!
From Monevator
US stocks vs the world: how often does the lead change hands? – Monevator [Members]
Reasons not to downsize in retirement – Monevator
From the archive-ator: On the plateau – Monevator
News
Note: Some links are Google search results – in PC/desktop view click through to read the article. Try privacy/incognito mode to avoid cookies. Consider subscribing to sites you visit a lot.
Energy price cap to rise 10% to £1,717 on latest review – Ofgem
Millions targeted by pension scammers… – Which
…and lookout for the ‘business tax compliance’ scam letter, too – Which
“Time has come to cut rates” says US Fed chair in policy shift – Reuters
Former Sunak adviser urges Labour to introduce wealth tax on housing… – Guardian
…as UK house prices drop more than £5,000 in August – Yahoo
Martin Lewis calls for rethink on Winter Fuel Payments – Guardian
Alzheimer’s drug lecanemab given green light in UK, but not for NHS – Sky
The economy is healing – Apollo
Products and services
Buy-to-let blues: rents rise as landlord purchases hit record low – Which
Britons going to Europe will need US-style Visa waiver from next summer – T.I.M.
How to get the best deal on a UK mortgage – Guardian
Open an account with low-cost platform InvestEngine via our link and get up to £50 when you invest at least £100 (T&Cs apply. Capital at risk) – InvestEngine
Santander Edge Saver: earn 6% interest – Be Clever With Your Cash
Is now the time to fix your savings? – Which
Homes for sale on islands, in pictures – Guardian
Comment and opinion
A cautionary tale of stock market forecasting – Novel Investor
Millennials are becoming Boomers – A Wealth of Common Sense
How much income could you retire on and is it enough? – Vanguard
Avoiding bad guys – Humble Dollar
The return of economic idiocy – Cliff Asness via X
A tree isn’t forest, a stock isn’t a portfolio – A Teachable Moment
Why investors will always be buying US bonds even as debt swells – Sherwood
Kamala Harris has the right ideas on housing [US but relevant] – Noahpinion
Navigating the nonsense mini-special
Fooled by the truth – Annie Duke
The C.R.A.P. framework for dealing with bullshit – Klement on Investing
Naughty corner: Active antics
20 pandemic-era darlings that arced from boom to bust – Investment Talk
Debunking dividend-investing myths [Podcast] – The Long-Term Investor
The economy-wide bullwhip effect – Axios
Berkshire Hathaway the mutual fund – Morningstar
We’re in a godawful vintage for VC funds – Sherwood
The summer of Bill Ackman’s discontent – Institutional Investor
Kindle book bargains
The Happy Index by James Timpson – £0.99 on Kindle
Freakonomics by Steven D. Levitt – £1.99 on Kindle
Smarter Investing by Tim Hale – £9.29 on Kindle [£9.29! But rarely reduced]
Rebel Ideas: The Power of Diverse Thinking by Matthew Syed – £0.99 on Kindle
Environmental factors
The promise of vertical farming – Uncharted Territories
Charts reveal how air quality has changed around the world – The Conversation
Rat-sized spiders make comeback in UK after nearing extinction – ITV
The European boats fishing under a veil of secrecy [Search result] – FT
Tesla owners are to get live updates of EV cost savings – This Is Money
A new wave of climate claptrap [Search result] – FT
Robot overlord roundup
Chubby, the AI-generated cat who could be the future of the Internet – BBC
Waymo’s robotaxi depot is still honking its neighbours awake – The Verge
ASML: AI’s laser show – Sherwood
AI-powered coding pulls in almost $1bn of funding to claim ‘killer app’ status [Search result] – FT
A running list of all the organisations partnering with or suing OpenAI – Sherwood
Off our beat
The great wealth wave – Aeon
Here’s why we must fight the Musk and Trump army – Prospect
Somebody has already figured it out for you – Raptitude
The woke pendulum – Uncharted Territories
The gender life expectancy gap is growing. So what’s killing men? – Men’s Health
I was a pawn in a chess game says teen swapped for Putin hitman – BBC
“The sub-Reddit that radicalised me against small penis jokes” – Slate
I wag, therefore I am: The Happiness of Dogs – Guardian
Stephen Hawking proved wrong about the most extreme black holes – Quanta
On the clock – Humble Dollar
The power of super-citizens – The Garden of Forking Paths
And finally…
“The world clings to its old mental picture of the stock market because it’s comforting; because it’s so hard to draw a picture of what has replaced it; and because the few people able to draw it for you have no interest in doing so.”
– Michael Lewis, Flash Boys
Like these links? Subscribe to get them every Friday. Note this article includes affiliate links, such as from Amazon and Interactive Investor.
![How to build an index-linked gilt ladder [Members]](https://monevator.com/wp-content/uploads/2023/11/index-linked-gilt-ladder-1-1024x212.png "How to build an index-linked gilt ladder [Members]")
Here are 3 ways to track the Vanguard FTSE global all cap within a google sheet, I imagine excel is similair.
They don’t work at all times of the day, so I have a formular to take whichever is working
Morningstar
=index(split(index(importhtml(“http://www.morningstar.co.uk/uk/funds/snapshot/snapshot.aspx?id=F00000XXVV” ,”table”,4), 2,3), ” “), 1, 2)
FT
=index(importxml(“https://markets.ft.com/data/funds/tearsheet/summary?s=GB00BD3RZ582:GBP”,”//span[@class=’mod-ui-data-list__value’]”),1,1)
Vanguard
=IMPORTDATA(“https://api.vanguard.com/rs/gre/gra/1.7.0/datasets/urd-product-details?path=[id=vanguard-ftse-global-all-cap-index-fund-gbp-acc][0].navPrice.value”)
The Alzheimer’s “wonder” drug is no such thing and the media should be ashamed of their dim witted reporting of it as such. It is absolutely the correct decision to not fund it on the NHS.
> Naturally this value includes the asset that is my house, as well as the liability of my mortgage.
You London lot probably have to do that because of prices in the Great Wen, which amplify the proportion as a part of your NW. The main value of the ‘asset’ that is your house is intangible. It keeps loathsome landlords out of your life. The memory of landlordism is still strong with me, thirty-five years after I last dealt with one of that breed. It doesn’t seem to have got any better since.
On spreadsheets, the devil in spreadsheets is that they are dreadfully hard to QA, as the logic is invisibly spread over many many cells, so errors can propagate. You can mitigate that to some extent computing the same result in multiple paths for a checksum. I gave up in the end, after 10 years of aggregate results. It was just getting unmaintainable, and from what I could tell git version control treats excel sheets as binary data, so you could roll back but not debug.
Now I use R and download historical values from Yahoo finance using an R library and then compute results with PMwR (portfolio management with R). I don’t have your exotica 😉 Yahoo are blighters for using different scale factors for some funds (pence and pounds) and occasionally changing that. I then feed that Yahoo data into Quicken 2004 which predates agile software development so it still works properly as opposed to modern release and patch practice for a cross-check. Quicken updates the journal date value with each update, but doesn’t make results depend on earlier values, which was what started to cause me pain with the Excel spreadsheets. Or maybe I am just a sloppy coder.
I am surprised that people don’t generally do this online, though heartened by Monevator’s readers’ apparent distrust of keeping financial information in the cloud. Maybe they’re becoming boomers like those millennials in your AWOCS link 😉
@Paul – have a metaphorical upvote for:
“They don’t work at all times of the day, so I have a formular to take whichever is working”
I’ve experimented with scraping, but never thought to have the sheet try until it found a working source.
Unfortunately for me, right now all three are spitting out errors in google sheets 🙁
It seems updating the way you scrape fund and ETF prices changes every couple of years. I settled on an excel sheet that scrapes closing prices from the FT site that has held me in sted for a while, although I had a day earlier in the week when my macro wasn’t working, but has since started to work again. I cribbed this from somewhere else, so no good on the technical side I’m afraid.
Main issue is that it pulls in closing prices from yesterday for UK based investments, but my Australian based investments get today’s closing price. Have never worked out how to avoid this, so just manage it at month’s end and don’t worry about it.
Have been using the same spreadsheet more or less for 9 years now, even my wife has started to trust this investing mallarky. I try not to reflect too much on the 2008-09, 2001-02, 1987 crashes, and really have no idea how to interpret the 2020 Covid blip.
Ermine-Quicken 2004 does it for me too
Only 3 trackers so just download current values from the Internet once a week
Also run a dummy portfolio on Trustnet which gives me my portfolio value in real time if required
One aspect of Quicken 2004 I have found invaluable is the Portfolio Rebalancer function under Analysis tab in the Investing Centre
Kept me right re where to put monies in Accumulation and now has informed me from where to take monies in Withdrawal phase and retain my required Asset Allocation
xxd09
I’m happy currently with downloading a suitable watchlist from HL every month. I think, as you mention, I got this idea from FvL.
Prior to that I was using googlefinance calls from a google sheet, which was ok but didn’t cover everything (e.g. VGLS funds) so I had to get them manually every month which was a pain.
Hats off to ermine for using R, but I cannot see how that could be easier than a spreadsheet. I can’t see me knocking up an equivalent to my spreadsheet in python any time soon…
@Rhino Here’s a starter for 10 in Python for getting the VWRL price from Yahoo! Finance!:
import requests
r = requests.get(“https://query2.finance.yahoo.com/v8/finance/chart/vwrl.l”, headers={
“User-Agent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36”
})
print(r.json()[“chart”][“result”][0][“meta”][“regularMarketPrice”])
@xxd09 > One aspect of Quicken 2004 I have found invaluable is the Portfolio Rebalancer function under Analysis tab in the Investing Centre
I’ve never used this, will investigate – thanks for the tip!
@Rhino #7 I’m not saying R is easier. But I had a tendency towards indiscipline and spaghetti code at the best of times, and that hasn’t got any better once I stopped working. The libraries quantmod and PMwR do the grunt work of downloading and analysis and profit/loss, thus saving me from doing my own Excel coding. Which is OK in small quantities but not in cumulative calculations across many years, leastways not in my hands. Know thyself and all that.
Besides I wanted to learn R to munge some scientific data and starting out with a field I sort of knew helped get me going.
current google sheets app script to grab fund price from Yahoo Finance:
function yahooF(ticker) {
const url = `https://query1.finance.yahoo.com/v8/finance/chart/${ticker}`;
const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
const contentText = res.getContentText();
const data = JSON.parse(contentText);
// Check if the result exists and has data
if (data && data.chart && data.chart.result && data.chart.result.length > 0) {
const regularMarketPrice = data.chart.result[0].meta.regularMarketPrice;
console.log(regularMarketPrice);
return regularMarketPrice;
} else {
console.log(“Error: Unable to retrieve market price.”);
return null;
}
}
then put eg
=value(yahooF(“0P0000TKZK.L”))
in your cell will get eg lifestrategy60Acc
it’ll break whenever yahoo changes things enough.
[Pedant warning] the Fig 1 table in the Aeon piece in the links is obviously wrong. It overstates the average wealth of the six countries’ citizens by a huge margin. Check out Wikipedia’s table on average per capita wealth by nation for correct figures and their sources. I saw the Aeon article at the time and thought that the Fig 1 table just had to be wrong as it would mean that on average a citizen of the 6 countries would be a millionaire.
I typically just copy-paste the current valuation of each fund from an internet search once a month/quarter. Perfectly able to automate it but I find the ritual soothing, and it also acts as an incentive not to add too many funds to avoid more faff. A bit like tracking gym weights with pen & paper instead of an app, there is something about it. To me, what is more important than live prices are things like accounting historic spending for inflation, but that data can be integrated once a year.
Thanks for the links.
Such a useful post, thank you @TI. Loving reading the multitude of solutions from the techie collective. Perhaps in c. 2 more years I’ll play with something more sophisticated.
Inclined to agree with @ermine about outsourcing to the cloud, but find Morningstar’s Portfolio Manager hard to beat, especially as entire portfolios copy paste so neatly into Excel template and thence to onward jiggery-pokery. So simple.
I do like to include the house value in the net worth tracker, partly out of vanity (makes me a millionaire!) but mostly because without a legacy requirement, the equity is not entirely illiquid and its potential for part release further down the line (more likely than downsizing) is part of the safety net calcs.
A few simple headline figures on the back of an old envelope for me these days. Less is more.
The below SMF (Stock Market Functions) addin for Excel pulls a huge range of data and has been kept updated so far
https://climbermel.github.io/SMF_Add-in/
After playing Excel, I use https://www.portfolio-performance.info/en/ and very happy with it.
+1 for Morningstar Portfolio Manager.
@Rhino, great minds think alike. I use an HL watch list as well. Download, copy and paste into my spreadsheet and use vlookups to get the prices into tabs for each account. Easy to do with no need for currency conversions or worrying about securities priced in pounds or pence. I only do it once or twice a year, so don’t need anything more complicated. We don’t bother with property values, but do include cash deposits which have to be manually updated and our mortgage, which we paid off this month.
The only niggle I have found is that some of our US listed ETFs have incorrect values if the download is done outside US trading hours.
One complication is a calculation estimating the LTA charge we would pay at age 75. Hopefully that has now gone for good, but I will not remove the calculation until the October budget, just in case.
The excel stock market functions work well enough, but I don’t want my spreadsheet to be updated every time I open it. I just want a snapshot which I update when it suits me.
I used Morningstar Portfolio Manager for a while until one day I discovered my portfolio had disappeared into the ether, never to be seen again – no idea what happened, but I was so disheartened at all that effort lost I’ve never bothered setting it all up again.
Thankfully I don’t need a spreadsheet as I only invest in global equity trackers through one broker for our SIPPs/ISAs/GIAs so I can just login and add up my wife and my accounts, then add 50k for premium bonds.
Before you can hope to improve something, you must measure and monitor its workings. This sort of spreadsheet TI is talking about is a great tool to monitor and measure the inner workings of your investments.
I wrote my portfolio and net worth tracking spreadsheet back in 2016. Portfolio valuations have always been entered manually once a week both in pounds and in USD. Over the years, I have added more and more features. The spreadsheet calculates the annualised return and volatility of each portfolio both for the current year and since inception. It estimates future values for various scenarios for 3 months ahead, a year ahead, two years, 5 years, and 10 years. It calculates potential annual incomes from the total portfolio assuming a range of withdrawal rates from super conservative up to 4%. It plots the total valuation from inception until now, where I can see the exponential growth with the superposed market fluctuations.
As a stoic, one of the features I find most useful is that it calculates what the total valuation and the withdrawal income would be if the equity allocation dropped by various percentages, as in previous market crashes.
I am now looking to move from a spreadsheet to either Python or R, as I absolutely hate spreadsheets!
@naeclue I also found this annoying but you can pull a share price time series / history and lookup for a given date. There is a template on their website
https://climbermel.github.io/SMF_Add-in/templates.html
@TMDW — Oh I like this! A project for the autumn perhaps.
Incidentally I don’t feel this is masochistic, in case any readers do, nor that your projections are being overly complacent. As you say, the more we understand our portfolios as investors, the better, whether we want to do anything to improve them or not.
As much as anything familiarity breeds reassurance when ‘stuff’ happens. Being through previous cycles of stuff helps big time, but as we age and our priorities change — and our portfolios grow, hopefully — our responses will in the future. So getting a handle on that ahead of events is potentially very valuable IMHO.
Boon and bane.
I have a self-built wealth spreadsheet. One feature is that on first glance my data entry page, which is deliberately in alphabetical order for ease of item location, looks very similar to the seperate pages where I display the data in non-alphabetical order via a conversion feature that I created.
The boon is that it is very easy to operate whilst still producing the results that I want. The bane is that it is very easy to forget which page is being viewed when in data entry mode, and inadvertantly over-write the formula in one of the cells in a display page, and beggar up the overall results.
The solution, which I found well worth implementing, was to add a series of if/then/else error checks, whose alerts are designed to trigger a single cell warning in majuscules at the foot of both the data entry page and the display pages. The errors do still occasionally occur but they are now flagged and can be corrected.
KISMIE – keep it simple make it effective.
@TI:
Nice links, thanks.
Re trackers: a fascinating read.
“Before you can hope to improve something, you must measure and monitor its workings. ” I first learned this lesson nearly forty years ago when I somehow got involved (the reasons escape me now) in a company-wide quality improvement initiative. That experience also taught me the companion lesson that: you better measure the right things as there is a very real possibility that unmeasured things will go to rat shxt in the mean time!
I update a suite of (mainly multi-tab) spreadsheets monthly. The eldest of which is over twenty eight years old. Other sheets have come and gone as their utility faded or they timed-out e.g. no need to monitor mortgage over-payments once the loan is cleared.
One thing I have done, which I have not seen mentioned so far, is to split the journey into distinct phases, such as: say, accumulation with DB, accumulation with DC, de-accumulation before DB started, de-accumulation after DB started but before SP started – my current phase- , etc
I have always found analysis of all the information collected to be very valuable for looking backwards, but, without fail, of very limited use in looking forwards. To date, my [most-likely] projections have always been rather conservative; but maybe that is not a bad thing?
Over the journey to date, more spreadsheets have been added than removed, such that I am currently updating around ten spreadsheets per month. I need to simplify and reduce this, as I used to get by (during the accumulation phase) with many fewer! There was an intentional increase in my monitoring as I jumped ship and I feel that I need to back off somewhat now. I have made some progress along these lines but it has been slow going.
P.S. I have been doing something similar to the monthly market collapse what-ifs TBDW mentions for at least a decade IIRC, and even armed with such info, all drawdowns are still, to me at least, a tad scary – although I am much more accustomed to them now.
P.P.S whilst I note in [#25] that my eldest tracking spreadsheet is >28 years old, some of my sheets contain data that goes back more than 40 years; sad git that I am!
I’ve got a 30-year old spreadsheet of my weight, it looks a bit like the ftse100. The volatility is a bit worrying!
My wealth tracking spreadsheet is very simple. I only update it every 6 months. I dont have any live prices, I just log into our ii (and other ISA) accounts and manually copy across the number of units/shares and price at that time. A few entries for premium bonds and some other non-stock market assets and I’m done. I try not to worry about fluctuations in between.
It did help me visualise the ISA accounts and holdings I needed to simplify/consolidate, giving me the impetus to get on with several ISA transfers.
I update my spreadsheet once per month and leave it at that. Armchair passive investing type myself so I much prefer it that way. I am not too fussed on what it’s doing other than that really. I also only do that because I manually invest each month. It’s a bit of a ritual and I always celebrate investing day with a take away and always try to be mindful and to have some gratitude of simply being on this FI path of mine.
I do however track my expenses to the penny and record what I spend in different spending categories. I really enjoy doing this and love looking at spending trends over the months/years. That’s one area where my spreadsheet is a bit more complex and checked frequently
TFJ
Cheers for an interesting set of links, TI – never thought that me going about the neighbourhood with my litterpicker has me on track to become a ‘super-citizen’ (from “The power of super-citizens” link) – go me!
@eremine On version controlling spreadsheets, may I recommend .fods extension?
Excel files are treated by git as binary files because they’re zipped folders of xml. Excel’s biggest OpenSource alternative, Libre Office Calc, also compresses the files in it’s default file format, .ods.
However, it has a trick up it’s sleeve – .fods, flat xml ods, an uncompressed variant of the .ods format. Version controlling XML is not the prettiest, but it does work.
Obviously not as version controllable as real source code, be that a jupyter notebook or R, but it’s a middle sophistication option
Intriguing inclusion @TI of Invest Talk’s top twenty “everything bubble” story stocks from 2021 that have since hit the rocks. Are you thinking that 2024 could be to 2021 what 2003 was to 2000 (?) and taking a punt 😉 It might be less of a case of catching falling knives, and more about counting up the dead cat bounces. Only five of the twenty are profitable, and one of those is at a nose bleed P/E. Etsy (from $38 bn down to $6.2 bn) and Zoom ($162 bn to $18 bn) do at least sport some name recognition, and now also at below average US large cap market P/Es. And to think that at one point in 2020/21 an exceedingly inflated Zoom was very briefly worth as much as a very depressed Exxon! No prizes here for guessing the wooden spoon in the terrible twenty list of fallen pandemic stars. Virgin Galactic has the honour having been an obviously very bad idea before the IPO, at the IPO and at all times since the IPO. A Galactic Turkey. How could anyone have valued it at $14 bn? For that matter, come to think of it, how can anyone think that it’s still worth $187 mn now?
@pikolo good to know there is a better way, but what I really want version control to do in excel is to be able to step back one formula fiddle in a cell at a time, sort of like a multiple ctrl-z, which I think gets flattened on save.
Spreadsheets are evil from a code design POV with their non-separation of data, presentation and logic all smooshed together. AlCam clearly shows it can be done, but he is a much more painstaking and careful worker than I.
A bit off piste but a couple of recent items relevant to index trackers
Some disquiet re “foreign domicile” of OIECs and ETF- Ireland and Luxembourg are favourites holding areas -under EU jurisdiction-relevance for U.K. investors if the worst happens
Plus a Humble Dollar article by Greg Spears re the size of index funds and their share of regulated institutions starting to cause problems with US financial regulators -perhaps leading to restrictions in index funds ability to follow the index
xxd09
@Ermine (#33):
Re: “AlCam clearly shows it can be done, …”
I do tend to be careful when spreadsheeting* and have developed some methods & rules to help assure longevity (such as no embedded VBA code or the like). However, some of my sheets are truly showing signs of age and most would probably strike others as bat shit crazy!
I have had a few narrow escapes over the years, which I believe (ie I cannot be 100% sure) I have ultimately managed to unpick. In the worst cases I have been able to recover things to a known point because I happen to use a couple of independent back-up strategies on my PC.
*although I am far from averse to knocking up a quick sheet or two to try ideas out – most of these I later ditch
@xxd09: the Humble Dollar piece “Unwanted Attention” published today which you highlight is very important as it segues into the issues raised in the research paper “In Search of the Origins of Financial Fluctuations: The Inelastic Markets Hypothesis”, by Xavier Gabaix and Ralph S.J. Koijen (first published 2020, last revised 2022) on the Social Sciences Research Network site, and to the various subsequent research; which has most notably been covered by Michael Green in his 26 March 2024 “Be Better” piece on his Substack “Yes I Give A Fig” and in his three recent interviews on YouTube by Barry Ritholtz, Michael Gayed and Rational Reminder.
[I’ve covered this previously, inter alia, in my comments #37 on the 9 February and #17 on the 13 June Monevator weekend reading threads.]
In particular:
– Check out the first table in Mr Green’s Substack post on the convexity surface of the impact upon volatility of passive fund flow share with increasing passive share of fund flows amongst all fund flows.
– Passive allocates by market cap but liquidity does not scale linerally by market cap. There is relatively less per unit of market cap in the largest companies.
– The Efficient Market Hypothesis assumes that collective rational judgments set prices, and that fund flows per se are neutral. But the inelastic market hypothesis demonstrates that $1 of flows leads to $3 to $8 (most likely $5) of change to prices with (most likely) active manager fund flows of $1 raising prices by $2.50 and passive fund flows of $1 increasing prices by (most likely) $17.
– At the moment passive share is 35% of market cap (or 45% on a looser definition of passive). In less than 15 years it’s on track for 85%. The implications of this could be profound, and not necessarily wholly unproblematic.
@ermine – yes, you can avoid the worst evils of spreadsheets if you carefully separate data sheets from formula sheets etc
Happy to help if you hit any issues with R. I’m not familiar with PMwR but know my way around R code in general.
Overall, I’m in the ‘less is more’ camp when it comes to monitoring. I do think there’s at least a small risk of generating false confidence from past performance trends.
[NB: the “price” at my #36 means market capitalisation price. The $5 average change per $1 fund flow was I think done when passive share was estimated at 17%, so half or less the current estimate.]
Interesting to read about tracking portfolios in spreadsheets etc. I’ve been using a growing constellation of Google Sheets since about 2016 with live price updating. I don’t think anyone has mentioned that Google Sheets has built in version control which is useful and you can see the history of updates at a single cell level if you want to. Not quite as useful as git version control but it’s usable if you need to see changes. Another useful feature of Google Sheets is the Apps Script feature which can be used to do some automations (look under the Extensions menu..). When you’ve got your sheets live updating and adding up the Net Worth or FI number etc you might want to record the history of that value and other stuff in the spreadsheet. So what I do is create a history sub-sheet and the Apps Script runs every night and adds a row with dated values for what I need. Then I can graph it and do analysis in another subsheet or elsewhere.
I use Google Finance for ETF and share prices but funds are the problem – I also scrape the FT and HL site for that. I tend to go for sites that are HTML formatted in a way the Google functions can easily grab the data. A tricky one has been dividend yield – for that I found an easy site to scrape is Fidelity. I added dividend yield to track and model the potential income from my portfolios. The fund / ETF/ share price grabs sometimes go wrong especially in the middle of the night and sometimes live prices fail horrendously for various reasons and a whole bunch of sheets go into a face full of errors. To resolve this I have a TODO to make a master sheet of price data grabs such that if the live price fails from various sources it at least has the “last known” price which will be good enough for me. Then I’ll make all my sheets take price data from that. You can probably tell from all this I was a software developer before I retired, I had a full dose of using “Excel as a front end GUI for middle management” back in the day 🙂
@TI (#23) – I agree. It’s not masochistic at all.
I am looking forward to reading about your autumn project!
A PPS to #36: Alongside the Gabaix’s and Koijen’s paper I should also give a mention to Jean-Philippe Bouchard’s paper, “The Inelastic Market Hypothesis: A Micro structural Interpretation”, which was last revised in January 2022 (15 pages), and which can be found on SSRN here:
https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3896981
And to Valentin Haddad’s paper, “How Competitive is the Stock Market? Theory, Evidence from Portfolios, and Implications for the Rise of Passive Investing”, which was last revised this April (56 pages), and which can be found here:
https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3821263
Both worth a read on this subject.
The Gabaix and Koijen paper, for ease of cross-reference, is here:
https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3686935
One advantage I find for maintaining my own difference engine type solution for tracking my portfolio that I haven’t seen mentioned here (apologies if I’ve missed it) is that I will spend time tinkering with the tracking contraption I’ve built rather than buying and selling things in my portfolio. It’s a kind of fidget spinner for finances: by the time I’ve finished building a model of how an alternative portfolio could perform the novelty has worn off and I can see that it’s within margin of error of my current portfolio.
Re: NW trackers:
One other thing I noticed is that Nicks M’s article and the comments to date have all been silent on taxation. FWIW, I find that net figures (ie post tax or in some cases post estimated tax) are the more informative – but each to their own I guess. Perhaps noting an estimated tax bill as liability [for SIPP’s, non primary residence property, etc] could work too?
Personally I use GNUcash to track nearly everything, I’ve been using it for about 12 years now.
I have my income, expenses and investments all tracked in there.
12 years of data means I know what I spend on so can be confident in my budgets, I know how it’s changed over time.
It can be setup to pull fund values from Morningstar which is useful.
Roughly once a month I login to Nationwide, pull the OFX file for a couple of accounts transactions, map anything that isn’t done automatically and tidy up a few things like my Aviva work pension units.
I can chart the growth of my investments over time feel happy that 12 years ago I had hardly anything and now nearly paid off the house and grown my pension and ISA pots nicely.
The best spreadsheet template I’ve found so far for comprehensiveness is CompiledSanity which is worth paying for given how easy it makes pulling in tracking figures. https://cspersonalfinance.io/ (I am unrelated to the creator other than the fact that I have paid for the template myself) There is a free version to try too.