Most UK self-directed investors start with a spreadsheet. Sometimes it stays a spreadsheet for years. Sometimes a particular session — a missing dividend, a rate-limited price feed, a broker statement that mysteriously does not match the cell totals — pushes the investor to look at dedicated tools. The transition is rarely clean and the spreadsheet rarely dies completely; it usually demotes to a parallel tax-tracking workbook while the live view moves elsewhere.
This article is about the specific failure modes of spreadsheet portfolio tracking for UK investors — not generic complaints, but the actual functions and ticker conventions that misbehave. It is also about when a spreadsheet remains the right tool, which is more often than the marketing pages of every alternative would have you believe.
This is not financial advice. Past performance does not guarantee future returns. Consider speaking to an FCA-authorised financial adviser for personalised guidance.
How GOOGLEFINANCE actually fails
Google Sheets is the most common entry point for UK retail spreadsheets. The GOOGLEFINANCE function is undocumented in any depth, unsupported when it breaks, and its failure modes have been remarkably consistent for a decade.
Rate limits. Sheets imposes an undocumented rate limit on how many GOOGLEFINANCE calls a single sheet can make. With a portfolio of fifteen tickers refreshing every five minutes, you will hit the limit within a working day and start seeing #N/A in cells that worked fine an hour earlier. The recovery is unpredictable — sometimes minutes, sometimes hours.
Ticker format inconsistency. A single LSE-listed share has at least three valid ways to be referenced: LON:LLOY, LSE:LLOY, and just LLOY. Different versions of the function accept different formats; GOOGLEFINANCE("LON:LLOY") works for some users and not others. UCITS ETFs are worse — LON:VWRL works, LON:VWRL.L sometimes works, the ISIN IE00B3RBWM25 rarely works directly.
GBp versus GBP. UK equities trade in pence (GBp or GBX). GOOGLEFINANCE returns the price in pence. If you forget to divide by 100 in your portfolio total formula, your spreadsheet will think Lloyds Banking Group is worth fifty pounds when the share is fifty pence — a 100x overstatement of portfolio value. Detection is instant if you read the total; the bug is silent if you do not.
US tickers and currency. A US-listed stock returns USD. The function does not convert to GBP automatically. You need a separate GOOGLEFINANCE("CURRENCY:USDGBP") call multiplied through. If the FX function fails for any reason — and it does — your USD positions silently revert to being treated as GBP. Apple at $200 sitting in a portfolio total as £200.
No support for Irish-domiciled UCITS by ISIN. Many UCITS ETFs do not have a clean ticker that GOOGLEFINANCE resolves. ISIN-based lookups are unreliable. Workarounds include using LSE listings (LON:VWRL) which works most of the time, or scraping from a third-party site, which works until the third-party site changes its HTML.
Stale prices outside US market hours. US tickers can return last-close prices for hours after the LSE close. UK tickers stop updating at LSE close. A 7pm refresh shows mostly real numbers and some hours-old numbers; the cells do not flag which is which.
These are not user errors. They are the function's actual behaviour. Investors who use GOOGLEFINANCE for years develop a folk knowledge of which cells to trust on which day.
How Excel STOCKHISTORY and the Stock data type actually fail
Excel's stock support is more reliable than Google's, and more limited.
The STOCKHISTORY function and the linked Stock data type require Microsoft 365 — they do not work in standalone Excel licences or older versions. The data comes from Refinitiv (via Microsoft's licensing), which is a different vendor from Google's source, with different coverage gaps.
Coverage gaps for UCITS ETFs. Many Irish-domiciled UCITS funds resolve cleanly in Excel. Some do not. The pattern is not predictable from outside — small or new ETFs often miss, big Vanguard or iShares funds usually hit.
Currency handling. The Stock data type returns the listing currency. Mixing GBP, USD and EUR positions in one workbook still requires manual FX columns, with the same failure modes as Google's currency function — an FX column that breaks silently leaves your portfolio total wrong without flagging.
Refresh latency. Excel's price refresh is slower and more cache-heavy than Google's. "Live" prices are often delayed by fifteen to thirty minutes, which is fine for most retail purposes but worth knowing.
Dividend backfills. Neither Sheets nor Excel automatically populates a historical dividend series for a holding. You can pull a recent yield via a separate function, but reconstructing past dividend payments — for tax or for total-return tracking — requires manual work or a third-party data source.
Share splits, ticker changes and corporate actions
The single largest source of long-term spreadsheet drift is corporate actions that the spreadsheet does not know about.
Share splits. A 4-for-1 split divides the share price by 4 and multiplies the share count by 4. The price feed reflects the split immediately on the ex-date. The share count column in your spreadsheet does not — unless you remember to update it. The result: the position appears to have lost 75 per cent of its value overnight. The spreadsheet does not flag this; you discover it by puzzling over an absurd loss number.
Ticker changes. Companies change ticker symbols more often than retail investors expect. Mergers, rebrands, delistings and re-listings on a different exchange all happen. When a ticker changes, your GOOGLEFINANCE cell starts returning #N/A. The fix is finding the new ticker and updating every reference — which works as long as you notice the #N/A.
Delistings. When a company is taken private, acquired for cash, or moved to the OTC market, the price feed stops. The spreadsheet returns the last known price indefinitely or #N/A, depending on the function. The position needs to be manually marked as closed.
Rights issues and bonus shares. Adjustments to share count and effective cost basis from rights issues require manual entry. The price feed reflects the new share price; the holding count and cost basis do not.
Dividend reinvestment. A DRIP (dividend reinvestment plan) issues additional shares periodically. The spreadsheet has no idea this happened unless you record each reinvestment manually. A DRIP held for ten years and never recorded silently understates your position by 20 to 40 per cent depending on yield.
For a portfolio with one or two corporate actions a year, manual maintenance is workable. For a portfolio with fifteen holdings and three or four corporate actions per holding per year, the cumulative bookkeeping load is real and easy to fall behind on.
FX history gaps
A UK investor with US holdings needs historical USD/GBP rates at every transaction date to compute UK-tax-relevant cost basis. Live FX is one cell; historical FX is harder.
GOOGLEFINANCE supports historical FX (GOOGLEFINANCE("CURRENCY:USDGBP", "price", DATE(2023,1,15))) but with rate-limit and reliability issues. Excel's STOCKHISTORY supports it more reliably for major pairs but with the same Microsoft 365 requirement.
The structural problem: a CSV import from a US broker shows your trade in USD. To compute the GBP cost basis (for HMRC purposes if held in a GIA, for sanity-checking against your broker statement), you need the USD/GBP rate on the trade date. Doing this in a spreadsheet works; doing it for fifty trades over five years across multiple brokers requires building a small FX-lookup machine that does not break.
When a spreadsheet IS the right tool
It would be dishonest to argue that nobody should use a spreadsheet. For some shapes of portfolio and some kinds of investor, a spreadsheet is genuinely the best answer.
Portfolios under five holdings. The bookkeeping load is small enough that the failure modes do not compound. A single ETF and a single bond fund tracked in Sheets is fine.
Investors who want to understand the mechanics. Building your own portfolio tracker is one of the best ways to understand cost basis, FX conversion, total return versus price return, dividend treatment and drawdown calculation. The act of doing it forces you to learn the maths.
Tax-tracking workbooks. Even investors who switch to a tracking app often keep a parallel spreadsheet for HMRC: realised gains, dividend income, transaction-by-transaction records. The accountant prefers a CSV anyway. A purpose-built spreadsheet for Self Assessment is often cleaner than exporting from a tracking tool.
Portfolios with one custodian and clean exports. If your entire portfolio sits with Trading 212 or AJ Bell or Hargreaves Lansdown, and the broker's CSV export is clean, you may not need anything beyond the broker's own statements. A spreadsheet that consumes the CSV directly avoids most of the GOOGLEFINANCE failure modes.
Strong opinions on what the dashboard should look like. Off-the-shelf trackers have opinionated dashboards. If you want a particular layout — say, a dividend calendar in one tab and a CGT-allowance harvest tracker in another — a spreadsheet you control will always beat trying to bend a third-party tool.
For most UK self-directed investors with three or more wrappers, ETFs from multiple issuers, and a few years of history, the breakeven shifts to dedicated tools. For a focused portfolio with one or two custodians, a clean spreadsheet outlasts the average SaaS subscription.
The easiest spreadsheet-to-app migration
If you do decide to leave the spreadsheet, the migration mechanics are routine.
Step 1 — pull broker CSVs. Every UK broker exports trade history as CSV. For most investors this is the cleanest source of truth, since it includes broker-specific quirks (charges, FX rates applied, settlement dates) that your spreadsheet may have approximated.
Step 2 — sanity-check against the spreadsheet. Spot-check a few holdings. If your spreadsheet and the broker statement disagree on cost basis, the broker is usually right (it has the trade record); your spreadsheet may have missed a corporate action.
Step 3 — import to the new tool. Most portfolio trackers — Sharesight, Snowball, Invormed, Kubera — accept CSV in either the broker's native format or a generic trade-history format. Sharesight has the broadest broker-format support; others may need a one-time mapping.
Step 4 — verify positions. Cross-check current holding quantities and cost basis after import. ETFs with DRIPs and any holdings with stock splits are the usual sources of import discrepancy.
Step 5 — keep the spreadsheet running for one quarter. Dual-running for a quarter catches differences before you commit. Most investors find one or two minor discrepancies that are easier to fix in the first month than six months later.
The total time for a typical multi-wrapper UK portfolio is two to four hours. The tedious part is verification, not import.
FAQ
Why does GOOGLEFINANCE keep returning #N/A?
Three common causes. First, rate limits — Sheets imposes an undocumented cap and recovery is unpredictable. Second, ticker format issues — LON:LLOY, LSE:LLOY and LLOY are not always interchangeable, and Irish-domiciled UCITS ETFs often do not resolve by ISIN. Third, ticker changes — companies rename and remerge often enough that some of your cells will eventually refer to symbols that no longer exist.
How do I track GBp stocks in Google Sheets?
GOOGLEFINANCE returns UK equity prices in pence (GBp or GBX). To convert to pounds, divide the returned price by 100 in your portfolio total formula. The most common spreadsheet bug for UK investors is forgetting this divisor — your portfolio appears to be worth 100 times its actual value. Build the divisor into your portfolio template once and reference it consistently.
Will Sheets handle dividend reinvestment?
Not automatically. Neither Google Sheets nor Excel reconstruct historical dividend payments or DRIP share issuances unless you log them manually. Each reinvestment date adds one row to your transaction log. A DRIP held for ten years and never recorded silently understates your position by 20 to 40 per cent depending on yield level.
How do I track stock splits in Excel?
Manually. The Stock data type and STOCKHISTORY reflect the post-split price immediately on the ex-date. Your share count column does not — you need to multiply it by the split ratio yourself. The same applies to bonus shares and reverse splits. A useful practice: log the date, ratio, and a note in a corporate-actions tab so you can audit historical changes when reconstructing returns.
Should I just keep using a spreadsheet?
If your portfolio is under five holdings, lives with one or two brokers, and you enjoy maintaining the workbook — yes, keep using it. If you have three or more wrappers, ETFs from multiple issuers, FX exposure, and a few years of history with corporate actions piling up, the cumulative maintenance cost has usually crossed the threshold where a dedicated tool saves time and reduces errors.
What's the easiest spreadsheet-to-app migration?
Pull broker CSV exports, sanity-check them against your spreadsheet (broker is usually right where they disagree), import to the new tool, verify current positions, and dual-run the spreadsheet alongside for a quarter. Total time for a typical UK multi-wrapper portfolio is two to four hours, mostly verification rather than import.
Want a tracker that does the GBp arithmetic, ETF look-through and ISA / SIPP / GIA roll-up automatically? Invormed is in early access — join the early-access waitlist and we will let you in as we open up.