How to append to a time series and have existing formulas recalculate to account for new data?

[xperronex]

Hi all, this should be fairly basic.

I have a time series of stock prices in one sheet, and formulas on another sheet to calculate returns based on certain date ranges (1y return, 3y return, etc.)

When I append new data to the time series, I manually update all of the cell references in my formulas, which is a pain. Is there a way to have those formulas dynamically update to account for the new data? For example, if my current time series is 12/31/2010 to 12/31/2020, and I add data to the bottom from 1/1/21 to 3/31/21, I'd like my 1y, 3y, etc. numbers to automatically use the new endpoint of 3/31/21. Is there a way to do this? Do I need to create a sort of "reference table" with the preferred start/endpoints for my formulas? Thanks!

 

[DRSteele]

Which version of Excel are you using? If it's 365, this will be a snap.

 

[xperronex]

It is 365!

 

[DRSteele]

I hope this works for you. You will need to have function STOCKHISTORY, so I assume Excel 365 is adequate. You will need to know how spilled arrays work, too. If you are unfamiliar with these new Excel concepts, I direct you to some cool videos that explain them, like here, here, here, here.

Here is a way to calculate some monthly and yearly periodic returns. It does not require a large table of decades worth of prices because function STOCKHISTROY was built to do that. It also has the added advantage of reflecting stock consolidations and splits, which cause problems for maintaining databases of stock prices in Excel.

A problem here is that when trying to refer to dates in the past, sometimes those dates land on days that are weekends, holidays or trade-halt days, which results in an error. The solution is to put in a tolerance factor of 10 days prior to the date we're trying to look up. For example, if we want three months ago from April 1, that gets us Jan 1, so the formula will report the price for the last trade day, usually Dec 31.

MrExcel posts18.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
3				returns
4				months			years
5	stock {Excel data Type | Stock}	last price	last trade	1	3	6	1	2	3	5	7	10
6	Brookfield Asset Management Inc. (XTSE:BAM.A)	$ 56.17	4/1/2021	7.6%	6.7%	25.4%	42.6%	33.7%	67.8%	89.9%	183.6%	322.6%
7	Intact Financial Corporation (XTSE:IFC)	$ 156.78	4/1/2021	8.6%	4.0%	9.5%	32.5%	38.9%	61.9%	71.9%	126.2%	212.0%
8	ROYAL BANK OF CANADA (XTSE:RY)	$ 116.00	4/1/2021	5.7%	10.9%	24.0%	39.7%	13.2%	16.6%	54.5%	58.5%	91.2%
Sheet67

Cell Formulas
Range	Formula
C6:C8	C6	=B6.Price
D6:D8	D6	=B6.[Last trade time]
E6:G8	E6	=$C6/XLOOKUP(EDATE($D6,-E$5),INDEX(STOCKHISTORY($B6,EDATE($D6,-E$5)-10,$D6,0,0),,1),INDEX(STOCKHISTORY($B6,EDATE($D6,-E$5)-10,$D6,0,0),,2),,-1)-1
H6:M8	H6	=$C6/XLOOKUP(EDATE($D6,-H$5*12),INDEX(STOCKHISTORY($B6,EDATE($D6,-H$5*12)-10,$D6,0,0),,1),INDEX(STOCKHISTORY($B6,EDATE($D6,-H$5*12)-10,$D6,0,0),,2),,-1)-1

 

[xperronex]

Thanks very much for the response! The thing is, it has to reference a table of historical prices (hard to explain without giving too much away on my current position, so I’m trying to be cautious). Is there any way to do this in that fashion (as in, through the user continuing to append to the bottom of a time series)?

 

[DRSteele]

No problem. Tomorrow (after Easter Sunday dinner) I will work on the exact problem you have. I posted the above for everyone's benefit.