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

xperronex

New Member
Joined
Mar 2, 2009
Messages
10
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,407
Office Version
  1. 365
Platform
  1. Windows
Which version of Excel are you using? If it's 365, this will be a snap.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,407
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLM
3returns
4monthsyears
5stock {Excel data Type | Stock}last pricelast trade1361235710
6Brookfield Asset Management Inc. (XTSE:BAM.A)$ 56.174/1/20217.6%6.7%25.4%42.6%33.7%67.8%89.9%183.6%322.6%
7Intact Financial Corporation (XTSE:IFC)$ 156.784/1/20218.6%4.0%9.5%32.5%38.9%61.9%71.9%126.2%212.0%
8ROYAL BANK OF CANADA (XTSE:RY)$ 116.004/1/20215.7%10.9%24.0%39.7%13.2%16.6%54.5%58.5%91.2%
Sheet67
Cell Formulas
RangeFormula
C6:C8C6=B6.Price
D6:D8D6=B6.[Last trade time]
E6:G8E6=$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:M8H6=$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

New Member
Joined
Mar 2, 2009
Messages
10
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,407
Office Version
  1. 365
Platform
  1. Windows
No problem. Tomorrow (after Easter Sunday dinner) I will work on the exact problem you have. I posted the above for everyone's benefit.
 

Forum statistics

Threads
1,143,677
Messages
5,720,246
Members
422,272
Latest member
ginkgoVil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top