Arborbridge wrote:Yesterday, I inadvertently copied the March 2021 prices in place of the March 2020. I should have twigged because the dates were incorrect in the original and I altered them manually to make them fit the "mondays" - I just assumed the dates were wrong in the spreadsheet whereas in fact I was viewing the wrong year!
The table for March 2020 should look like this:-
This gives the highest yield as 3.83% on the 16th March, and would make the two peaks almost the same. This still doesn't give me an explanation as to why the "then" and "now" prices are different, where "now" means the closing price I can see today on Yahoo, and "then" was what I copied and pasted at some time in the past from Yahoo.
Checking up using the FT again, your "now" prices are the closing prices for the
days Monday March 2nd, 9th, 16th, 23rd and 30th, 2020, give or take the odd 0.5p. Your "then" prices are the closing prices for the
Monday-to-Friday weeks containing those days - which are the closing prices for the last trading days in those weeks. The Easter bank holiday was some way into April in 2020, and so those final trading days were all Fridays: March 6th, 13th, 20th, 27th and April 3rd, 2020. With share prices having been particularly volatile around then, just one trading day could make a considerable difference to prices, and so there doesn't look to be any particular relationship between the columns...
Whether the difference is due to you doing something different between then and now, or to Yahoo changing their weekly prices facility over from giving closing prices for Monday-to-Friday weeks to closing prices for days at weekly intervals, I cannot tell...
Arborbridge wrote:Out of curiosity, I decided to find out whether Yahoo prices do change, or whether I'm imagining it - this is in the context of my conversation with Gengulphus about the error in my yield chart.
Below is a column of closing prices pasted from Yahoo on the day that I revised my chart this week, marked "Then" with the next column being marked "Today". Today being early this morning.
The previous past and copy would have been March 2021, so prices from January to March would have been copied at that time: price after March would have been copied this week and added to the column.
The final column on the right shows any changes, and indeed there are two.
I'm not clear what is going on, or whether I am making some of error myself, but there does seem to be an occasional problem.
Some more looking at the FT prices says that
* 3733 was the closing price on Friday, February 26th, 2021.
* 3920 was the closing price on Tuesday, February 23rd, 2021.
* 4144 was the closing price on Friday, July 23rd, 2021.
* 4050.5 was the closing price on Thursday, July 22nd, 2021.
Hypothesis: Yahoo gives you the closing price for the Monday-to-Friday week containing the date you specify - normally a Friday, but bank holidays can push it earlier -
and so can the week not yet being complete.
If that hypothesis is right, your table could be explained by you having updated your data at some time between the market closes on Tuesday February 23rd and Wednesday February 24th earlier this year, then updated it again at some time between the market closes on Thursday July 22nd and Friday July 23rd this last week (only doing the dates that you didn't already have data for, of course), and then done the "now" check today for the full range of dates. You've already said you did the last part today, so that's confirmed. The second part is more specific than your "the day that I revised my chart this week", but seems entirely consistent with you having posted
the chart at 07:16 on Friday July 23rd. Does the first part seem reasonably consistent with your recollection of when you previously updated your data for the chart? (I'm obviously not expecting you to remember exact dates 5 months ago! - but does about 5 months ago seem right?)
You could also try to get additional confirmation by asking Yahoo for the closing price for Monday July 26th (tomorrow) after the market close tomorrow and further days this week - if the hypothesis is right, it's likely to change on each of Tuesday, Wednesday, Thursday and Friday, and then stop changing. (But I'm rather doubtful this additional confirmation is necessary - it may well count as gilding the lily!)
Finally, as regards what to do about it assuming this is what is going on, I can see various alternative possibilities (no need to do more than one of them):
* Switch over to using daily data - this will be less likely to miss seeing sharp yield spikes (*).
* Switch over to using weekly data for Fridays rather than Mondays.
* Only update your data at weekends.
* Each time you update your data, mark the last data point in some way - I would probably give it a yellow background colour in the spreadsheet, as a reminder that it needs checking/updating. Update the data point that was left marked by the last update, remove the mark from it, then add further data points as far as you can and mark the last data point added.
(*) Though for the best way of catching sharp yield upward spikes, use "low" share prices instead of "closing" share prices, and for the best way of catching sharp yield downward spikes, use "high" prices. Those will guarantee catching the even very narrow yield spikes using weekly or even lower-frequency data - though it's a bit awkward if you want to show both types of spike...
Gengulphus