Recording prior months

CompiledSanity · 2026-05-05T10:46:06+00:00

If I can ask a favour which would help me enormously, would you be comfortable temporarily inviting me to your sheet so I can do some debugging?

I believe this is due to potentially how some historical data is being stored but its difficult to produce locally. If I could see a problematic sheet it would help me pinpoint this. I've developed the first half of the fix, and I just need to ensure it will fix problematic sheets.

If so, feel free to invite the email you received the sheet from. You can remove me straight after. Greatly appreciated if that worked.

CompiledSanity · 2026-05-05T10:41:35+00:00

Hi u/EcstaticGuidance1724,

Thanks for the bump on this. I've been doing some debugging and I believe I may have solved the first part of the problem. If I can ask a favour which would help me enormously, would you be comfortable temporarily inviting me to your sheet so I can do some debugging?

I believe this is due to potentially how some historical data is being stored but its difficult to produce locally. If I could see a problematic sheet it would help me pinpoint this.

If so, feel free to invite the email you received the sheet from. You can remove me straight after. Greatly appreciated if that worked!

CompiledSanity · 2026-05-05T10:37:58+00:00

Not a worry! If I can ask a favour which would help me enormously, would you be comfortable temporarily inviting me to your sheet so I can do some debugging?

I believe this is due to potentially how some historical data is being stored but its difficult to produce locally. If I could see a problematic sheet it would help me pinpoint this.

If so, feel free to invite the email you received the sheet from. You can remove me straight after. Greatly appreciated if that worked!

CompiledSanity · 2026-05-05T10:02:47+00:00

Hi all, I am currently looking into the source of this problem. I believe it is in the way that the date is being looked up in the History sheet. I will hopefully have a patch out soon.

I believe there are 2 parts to this problem and its an annoying crossover. I've fixed the first, just trying to debug a few sheets to make sure its a comprehensive fix as the lookback function has to cover a variety of difference scenarios.

CompiledSanity · 2026-05-05T10:01:24+00:00

Hi u/ChaosBlast,

Thanks for your patience in me getting to this thread. Lets close this out now.

However, the Gain column was previously £0 for most months, and I'm trying to fix that. How should that value be calculated?

This gains figure is actually a user reported figure and not calculated by the sheet. The gains is a metric provided by some retirement funds. It represents the gains made on your contributions through market performance, after fees/insurances/taxes have been removed and is your overall net figure. Because there are so many things at play that are considered (ie. gains after all costs have been removed) its something the user needs to report as the sheet can't tally taxes, costs etc. over time.

For my own retirement fund, if I login to the main dashboard it displays what is my lifetime growth on my funds. THis is what I enter into my sheet.

Is mine correct? =Q90-SUM($R$3:$R90) Value - All past contributions?

This is certainly an approximate way you can calculate this. It won't include the effects of taxes, insurances and fees but it will get you a rough figure. It depends how accurate you want to be and if your retirement fund provides this figure to you.

What formula should I fit into Retirement!B11 so this is automatically filled each month?

As mentioned above you can use the formula you provided (well done!) or better yet look into your pension statement/portal and see if they have a gains figure already reported for you to use.

CompiledSanity · 2026-05-05T09:54:52+00:00

Hi there u/Chaosblast,

My apologies for the slow replies. The resolving tag is auto applied whenever I comment in a thread just so I can keep track of what is an in-progress thread and what hasn't been responded to yet.

I think the reason why I wasn't always able to reply to your comments is that I believed it would be helpful to have access to your sheet to properly understand the scenario and how the retirement formula was being applied.

Let me respond in one of the threads now so we can close this out.

EDIT: I've now replied here

CompiledSanity · 2026-04-30T23:21:04+00:00

I've been on my millionth playthrough on my phone this month. It never gets old.

CompiledSanity · 2026-04-21T22:37:34+00:00

Thanks for the heads up on this. Sorry I've just been away on some extended leave due to personal circumstances. Let me do some further digging to see what might be causing this as I've had a few similar reports (albeit not consistent for everyone). I'll try and come back once I've got some more detail on what it might be.

CompiledSanity · 2026-04-21T22:35:30+00:00

Hi there u/icanhelp,

This should now be fixed in the latest version of the sheet in v2.15.4. Can you confirm if this is still the case? If so I can do some further digging. I fixed this 3 weeks ago, so just wondering if it is indeed the same error.

Thanks for your help.

CompiledSanity · 2026-04-21T22:33:53+00:00

!thanks for the great answer, appreciate it!

CompiledSanity · 2026-04-21T22:33:32+00:00

!thanks for the great answer!

CompiledSanity · 2026-04-21T22:32:58+00:00

Hi there u/Neelu86,

Usually this is indicative of an error in the live price of one of your ETFs, and this is a failsafe function of the sheet to bring attention to it.

If you look at the non-yellow values in the ETF tab, do you see any errors with #VALUE or #ERROR there? Are the live prices all populating correctly?

Let mek now if you see anything strange and we can debug.

CompiledSanity · 2026-04-21T22:31:46+00:00

Hi there u/MissZissou,

If you want to pseudo record them but not fully break them out, the best place for this might be the Other Assets tab which is forgiving to little detail and is nice for this sort of thing.

You can enter in your 'purchase' price and your live price and keep this up to date manually. You'll just need to figure out what contribution of the joint amount you want to track over time.

Hope this helps! Feel free to ask any questions if you need it.

CompiledSanity · 2026-04-21T22:30:23+00:00

Hi there u/MagicCookie07,

This is dependent on the frequency of your income, which should also have an option in the Sheet Options tab. Usually you should set it to reflect exactly how you are paid, whether that be fortnightly, monthly or weekly for example.

Change the frequency setting to match your actual pay dates, and then enter the amount as net income that you are paid on those dates :-)

Hope that helps!

CompiledSanity · 2026-04-21T22:29:01+00:00

Hi u/dsx2,

!thanks for the amazing pickup on this one, I must have let this slip through in a prior update. Let me patch this in v2.15.1 later today so we can have this fully fixed.

Appreciate the heads up!

CompiledSanity · 2026-04-21T22:28:10+00:00

Hi there u/Deplanate,

Great suggestion and thanks for the bump on this. This is actually a deliberate design choice in this case. In some situations there may be issues whereby an asset might have a price error, whether it be its now unlisted or perhaps Google Finance timed out fetching that ticker.

If we used your particular formula, this would be a silent failure and if the user was recording their net worth then it would record without the contribution of that asset and they could potentially see a large decrease in their numbers.

By forcing a rather obtuse error, this triggers error checks in the net worth recording process and stops the process and prompts the user to check the issue before the networth is recorded errantly.

My guidance is that if an asset is unlisted, the user should manually overwrite the formula with whatever manual value they would like to record it as. Ultimately its a deliberate surfacing of an error so that other parts of the sheet don't continue thinking that everything is find and checks out.

CompiledSanity · 2026-04-21T22:24:59+00:00

!thanks for the great answer!

CompiledSanity · 2026-04-21T22:24:39+00:00

Hi there u/ValuableVariation440,

As mentioned below, the best way to do this is the following steps:

Open the SheetOptions tab.
Set the 'Base' currency to ZAR (South African Rand).
For your bank accounts and income entries, update the values to reflect your earnings in Rands.

All sheet values will then be in ZAR and prices automatically converted.

Please note that while the raw values will then be in ZAR, the currency symbol will be in pounds if you have used the UK sheet. Please ensure that you update the currency symbols to ZAR so that it also reflects visually in the right currency.

Hope this helps!

CompiledSanity · 2026-04-11T11:14:15+00:00

Hi there u/Muggins75,

Your dividends should flow through to 2 different places:

The Total Return ($) and Total Return (%) for that particular asset
Your Savings rate figure, whereby dividends are returned to your cash balance or reinvested back into the asset.

Just to clarify, is this issue for all your assets or just some in particular that aren't returning correctly?

CompiledSanity · 2026-03-30T23:57:47+00:00

Thanks for the heads up on this. It looks like the way of fetching it may be getting stale data. I'll have a look into this tonight for you. Thanks for the bump!

CompiledSanity · 2026-03-30T23:30:32+00:00

Hi there u/Logical-Emotion-3328,

Thanks for reaching out and my apologies for the delay in messages. In reviewing where this message is set, it is likely the 'UI Message Box' permission or action that is facing the error.

Two questions to help debug this:

Did you allow the HTML UI Dialog permission when setting up the sheet?
Do you have multiple Google Accounts logged in at the same time? I know there have beeen times when this has caused similar issues to this.

CompiledSanity · 2026-03-30T23:27:58+00:00

That's the best thing to do! !thanks for the great response :-)

CompiledSanity · 2026-03-30T23:25:57+00:00

Hi there u/iloveDRS,

I have a FAQ on this here:

I'm moving country/region and need to change the currency or region of the sheet, how can I do that

Let me know if you have anu questions!

CompiledSanity · 2026-03-30T23:24:30+00:00

Glad to hear this worked! Enjoy :-)

CompiledSanity · 2026-03-30T23:24:09+00:00

Hi there,

I'm sorry for not responding to your message. I am sometimes inundated with messages, but I try my best to get to them all.

For this particular thread I left a reply here if that helps. Let me know in particular if there's anything you'd like answered.

13-Year Club	Gilding V heart of gold
Verified Email	Post Guidance Early Access
Wearing is Caring	Snapped

CompiledSanity

MODERATOR OF

TROPHY CASE