linear interpolation (1 Viewer)

[Sleekmac]

Hi Folks,
I have a problem I've been mulling over, and I thought I'd just post it here and see if maybe someone has already gone through this and might be able to tell me what I am missing. I have an access database that contains a table of currency rates by forward date. So for example, there are 90+ individual currencies in column 1, with 6-10 rates for each currency, each rate corresponding to a distinct "value date". I have another column which contains a concatenation of currency and value date, and this is my primary key. (ex. AUD10/12/06, EUR10/19/06, etc.) These dates are pretty much standard, but not always consistent for each currency. They all have a S (spot) rate, and most have a 1W (1 week) rate, a 1M (1 month), 3M, 6M, and 1Y.
At one point in the process, an append query is run that adds records to the table, leaving the rate field blank. The dates that are used fall somewhere between the given rates, for example 9 days, which would be two days after 1W, but 11 days before 1M. Then, another bunch of queries determine what the "last available" rate before the target rate is, and the "next available" one after it, and use these as the two givens to figure out what the target rate would be between them. The way I have this working is not ideal, and it works so far only because the rates it is interpolating have so far been less than 1 week out, or in a few cases between 1 month and 3 months. So since the data points are typically in those ranges, I have 3 cascading queries, the first assumes the target date is between 1M and 3M, the second assumes it's between 1W and 1M, and the third assumes it's between S and 1W. Each of these assigns the respective low and high points as the "last" and "next".
What would be better, is if a bit of VBA code (or even an SQL statement) could put the dates in order for each currency, and determine what the last record up is, use that as the "last available", and set the next record down as the "next available". This has turned out to be easier said than done.
It's easy to do in Excel with activecell.offset, but I'd rather keep it all in access.

 

[CraigDolphin]

I'm not sure if I understand your question, and I know less than nothing about high finance, but I may have encountered a similar issue interpolating salmon catch-rates between sampling strata on differing days (0 to many days apart).

What I start with is a table (tblScrewtrapCatchInterpolation1) generated from a crosstab query of ascending continuous dates (1 date = 1 record) that has four fields for my four sampling strata (Dawn, Day, Dusk, Night). My strata may be analogous to your currencies. This table has a value wherever a sample was taken in that strata, and a null where I need to create an interpolated value.

I then use vba to import the table as a recordset, then look at each field independently, using rst move.first, move.next, move.previous to move along the recordset looking for dates with values to use to interpolate for intermediate dates with no values. Then, once I have the value for each strata on a date, I add that into a separate table for each strata. I later pull together the four tables into a single table.

It's ugly and slow (written by me and I'm a biologist...be afraid! ), and I'm sure there must be better ways, but it works. If you want to look at the code I'll be happy to share so you can adapt or improve it. (Or run screaming into the night...)

 

[gemma-the-husky]

Am I correct in thinking that what you want to do for a particular currency transaction, on a particular date is to find the appropriate currency rate where the date of the set exchange rate is the highest date less than or equal to the date of your transaction. ie the effective date at the time of the transaction.

I have done something similar to find the price of daily fuel purchases from a list of spot purchases .

You need to ensure that all your exchange rates are loaded first.

If this is what you want let me know, and I'll look up the code.