# Interpolating and expanding time series

1. ## Interpolating and expanding time series

Hi

This is my first post. I have been struggling with an efficient way to modify my data set and I am looking for help. I have a data time series in 1 hour increments and would like to increase the increments to 3 minute intervals. The data would be interpolated between the hour intervals so that decimal increments will be added to the previous 3 minute interval record. I can do this manually over a couple of hours, but when the data is 14 days worth, it becomes very long and tedious process where mistakes can happen. Please see the attached sample file

Thanks

2. ## Re: Interpolating and expanding time series

Are you comfortable with a VBA solution?

 A B C D E F G 1 Orginal data Desired data 2 Date time data 3 5/10/2019 0:00 1000 5/10/2019 0:00 1000.00 F3: =IntIndex(\$B\$3:\$B\$12, IntMatch(E3, \$A\$3:\$A\$12)) 4 5/10/2019 1:00 1003 5/10/2019 0:03 1000.15 5 5/10/2019 2:00 1005 5/10/2019 0:06 1000.30 6 5/10/2019 3:00 1002 5/10/2019 0:09 1000.45 7 5/10/2019 4:00 999 5/10/2019 0:12 1000.60 8 5/10/2019 5:00 987 5/10/2019 0:15 1000.75 9 5/10/2019 6:00 996 5/10/2019 0:18 1000.90 10 5/10/2019 7:00 1001 5/10/2019 0:21 1001.05 11 5/10/2019 8:00 1003 5/10/2019 0:24 1001.20 12 5/10/2019 9:00 1007 5/10/2019 0:27 1001.35 13 5/10/2019 0:30 1001.50 14 5/10/2019 0:33 1001.65 15 5/10/2019 0:36 1001.80 16 5/10/2019 0:39 1001.95 17 5/10/2019 0:42 1002.10 18 5/10/2019 0:45 1002.25 19 5/10/2019 0:48 1002.40 20 5/10/2019 0:51 1002.55 21 5/10/2019 0:54 1002.70

3. ## Re: Interpolating and expanding time series

If you are in a position to use a spreadsheet other than Excel, this is straightforward in Gnumeric using that spreadsheet's built in INTERPOLATION() function =INTERPOLATION(\$A\$3:\$A\$12,\$B\$3:\$B\$12,E3,0) entered into F3 and copied down as far as needed.

If you cannot use a spreadsheet with a built in interpolation function, then you need to build your own algorithm. I recommend an approach like this (which uses helper columns in the lookup table, so some users don't like it):

1) Add 2 helper columns to the original data that compute slope and intercept for each segment. =SLOPE(B3:B4,A3:A4) in C3, =INTERCEPT(B3:B4,A3:A4) in D3 and copy down.
2) Where you want the interpolation, use lookup functions to return the slope and intercept for the appropriate segment and put them into the standard y=mx+b equation =VLOOKUP(E3,\$A\$3:\$D\$12,3,TRUE)*E3+VLOOKUP(E3,\$A\$3:\$D\$12,4,TRUE).

4. ## Re: Interpolating and expanding time series

That's a pretty good solution.

5. ## Re: Interpolating and expanding time series

Thank you so much. I don't mind using the helper columns. That worked great. I appreciate your help!

There are currently 1 users browsing this thread. (0 members and 1 guests)