VBA linear interpolation FX forward points

[macfoolish]

Hi community,

please bear with me this is my first post. I do have basic VBA knowledge but Im struggeling to achieve the following. I do have curve of forward pts from bloomberg or reuters as you can see from below html code.

What I try to generate automatically is the yellow area. Basically daily fwds point and Outrights, assuming start date is trade date. First issue is the quotation standard, all starts with the Spot Rate which is basically a forward outright rate which needs to be transformed using on and fwd pts.

Any ideas how to tackle that?

Sheet1

	A	B	C	D	E	F	G	H	I	J	K
1	Curve Date /Trade Date			16. Nov 17
2
3								VBA Output Linear Interpolation
4	Term	Start Date	End Date	Days	Fwd Pts	Outright		Start Date	Days	Outright	Fwd Pts
5	ON	16-Nov-17	17-Nov-17	1	1	1.199595		16. Nov 17		1.199595
6	TN	17-Nov-17	20-Nov-17	3	3.05	1.199695		17. Nov 17	1	1.199695	1.00
7	Spot	20-Nov-17	-	0	1.2	1.2		18. Nov 17	2	1.199797	2.02
8	SN	20-Nov-17	21-Nov-17	1	0.95	1.200095		19. Nov 17	3	1.199898	3.03
9	SW	20-Nov-17	27-Nov-17	7	7.1	1.20071		20. Nov 17	4	1.2	4.05
10	2w	20-Nov-17	4-Dec-17	14	14.3	1.20143		21. Nov 17	5	1.200095	5.00
11	3w	20-Nov-17	11-Dec-17	21	21.5	1.20215		22. Nov 17	6	1.200203	6.08
12	1M	20-Nov-17	18-Dec-17	28	27	1.2027		23. Nov 17	7	1.200304	7.09
13								24. Nov 17	8	1.200406	8.11
14								25. Nov 17	9	1.200507	9.12
15								26. Nov 17	10	1.200609	10.14
16								27. Nov 17	11	1.20071	11.15
17								28. Nov 17	12
18								29. Nov 17	13
19								30. Nov 17	14
20								01. Dez 17	15

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:70px;"><col style="width:70px;"><col style="width:70px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:70px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D5 =C5-B5 F5 =F6-E5/10000 D6 =C6-B6 F6 =F7-E6/10000 F7 =E7 D8 =C8-B8 F8 =$F$7+E8/10000 D9 =C9-B9 F9 =$F$7+E9/10000 D10 =C10-B10 F10 =$F$7+E10/10000 D11 =C11-B11 F11 =$F$7+E11/10000 D12 =C12-B12 F12 =$F$7+E12/10000 H17 =H16+1 I17 =H17-$H$5 H18 =H17+1 I18 =H18-$H$5 H19 =H18+1 I19 =H19-$H$5 H20 =H19+1 I20 =H20-$H$5 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4