VBA linear interpolation FX forward points

macfoolish

New Member
Joined
Nov 26, 2017
Messages
1
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

ABCDEFGHIJK
1Curve Date /Trade Date 16. Nov 17
2
3 VBA Output Linear Interpolation
4TermStart DateEnd DateDaysFwd PtsOutright Start DateDaysOutrightFwd Pts
5ON16-Nov-1717-Nov-17111.199595 16. Nov 17 1.199595
6TN17-Nov-1720-Nov-1733.051.199695 17. Nov 1711.1996951.00
7Spot20-Nov-17-01.21.2 18. Nov 1721.1997972.02
8SN20-Nov-1721-Nov-1710.951.200095 19. Nov 1731.1998983.03
9SW20-Nov-1727-Nov-1777.11.20071 20. Nov 1741.24.05
102w20-Nov-174-Dec-171414.31.20143 21. Nov 1751.2000955.00
113w20-Nov-1711-Dec-172121.51.20215 22. Nov 1761.2002036.08
121M20-Nov-1718-Dec-1728271.2027 23. Nov 1771.2003047.09
13 24. Nov 1781.2004068.11
14 25. Nov 1791.2005079.12
15 26. Nov 17101.20060910.14
16 27. Nov 17111.2007111.15
17 28. Nov 1712
18 29. Nov 1713
19 30. Nov 1714
20 01. Dez 1715

<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
CellFormula
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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