Hello
I need to do a two-dimensional linear interpolation. Nothing too fancy, except that some of the observations are error values (currently NA(), but I could change that).
This is financial concerning interest rates.
I want to do an interpolation that skips over any missing value on that particular day and uses whatever values are present.
A simplified version of the data looks like this.
From this data I am trying to extract the 5Y interest rate for each day in January. So for the 1 Jan, the interpolation target is 1 Jan 2030 and the value should be interpolated Apr-28 and Apr-31 data points. Same for 2 January, 3 January up until 6 January, with the target day slowly incrementing up.
However, starting 7 January, we have a new data series, the Apr-29. So from 7 January, when our 5Y target is 7 January 2030, we should now use the Apr-29 and Apr-31 data points as the interpolation.
Is here any way to make that happen easily?
I could write code to incrementally look left and then look right to find the data points, but I have several thousand data points so the sheet would be exceptionally slow. I figured between the new excel functions and the Python inclusions it might be possible to do this in a more sophisticated way.
Thanks in advance.
I need to do a two-dimensional linear interpolation. Nothing too fancy, except that some of the observations are error values (currently NA(), but I could change that).
This is financial concerning interest rates.
I want to do an interpolation that skips over any missing value on that particular day and uses whatever values are present.
A simplified version of the data looks like this.
| 21-Apr-27 | 21-Apr-28 | 21-Apr-29 | 21-Apr-31 | |
| 1-Jan-25 | 4.09 | 4.09 | #N/A | 4.04 |
| 2-Jan-25 | 4.13 | 4.01 | #N/A | 4.19 |
| 3-Jan-25 | 4.14 | 4.10 | #N/A | 4.11 |
| 4-Jan-25 | 4.06 | 4.15 | #N/A | 4.06 |
| 5-Jan-25 | 4.13 | 4.02 | #N/A | 4.04 |
| 6-Jan-25 | 4.09 | 4.09 | #N/A | 4.02 |
| 7-Jan-25 | 4.17 | 4.01 | 4.05 | 4.02 |
| 8-Jan-25 | 4.17 | 4.20 | 4.12 | 4.09 |
| 9-Jan-25 | 4.05 | 4.01 | 4.03 | 4.02 |
| 10-Jan-25 | 4.10 | 4.01 | 4.16 | 4.14 |
| 11-Jan-25 | 4.01 | 4.03 | 4.15 | 4.09 |
| 12-Jan-25 | 4.08 | 4.02 | 4.08 | 4.05 |
| 13-Jan-25 | 4.14 | 4.10 | 4.04 | 4.08 |
| 14-Jan-25 | 4.01 | 4.13 | 4.00 | 4.16 |
| 15-Jan-25 | 4.02 | 4.03 | 4.05 | 4.00 |
| 16-Jan-25 | 4.02 | 4.02 | 4.07 | 4.05 |
| 17-Jan-25 | 4.18 | 4.20 | 4.06 | 4.18 |
From this data I am trying to extract the 5Y interest rate for each day in January. So for the 1 Jan, the interpolation target is 1 Jan 2030 and the value should be interpolated Apr-28 and Apr-31 data points. Same for 2 January, 3 January up until 6 January, with the target day slowly incrementing up.
However, starting 7 January, we have a new data series, the Apr-29. So from 7 January, when our 5Y target is 7 January 2030, we should now use the Apr-29 and Apr-31 data points as the interpolation.
Is here any way to make that happen easily?
I could write code to incrementally look left and then look right to find the data points, but I have several thousand data points so the sheet would be exceptionally slow. I figured between the new excel functions and the Python inclusions it might be possible to do this in a more sophisticated way.
Thanks in advance.
