If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Range and Average F/X Rate
Hi Everyone.
I am hopeful that you will be able to assist me in coming up with a solution to a problem I am having... I have two tables: (1) An Accrual Table - Contains a "start date", "end date" and "daily accrual". (2) A F/X Table - Contains an "effective date", and "f/x rate" which is updated daily. My problem is that many of these daily accruals are in a different currency (i.e AUD/USD) than my domestic currency (CAD), and I need to compute all my results in CAD. Thus, when users pass in parameters (i.e. a profit start date and end date), I must compute the total amount accrued over this period in CAD, while the daily accruals are in foreign currencies. For example, if the profit start date is 01/01/2005 and the profit end date is 02/01/2005, then I must sum the daily accrual for EACH day in this range, using the above ranges in table one. The profit start date may overlap or intersect a given start and end date for the accrual range (in table one), so I cannot simply convert the accrual amount at the average prevailing rate during that period. Rather, I must figure out how many days of that given amnt of accrual, paying attention to the start and end date of the range, apply and convert each daily accrual at the prevailing spot rate (F/X rate) on that day, and then sum them all up. So, for 01/01/2005 my daily accrual might be 100, and my F/X 1.2, giving me a CAD accrual of $120, while the following day (with the same accrual) the F/X rate may have risen to 1.3 (so CAD accrual of $130). Somehow I need to, given the inputted profit start/end dates, calculate the daily accrual in CAD, getting the individual spot rates for each day in that range, and converting the daily accrual at that rate. Clearly I cannot just use a simple join, as my accrual table has only a range for a given record (i.e. 12/20/04 - 01/15/05), rather than each individual date. My thought had been to write a VBA function with the following inputs: (1) Start Date of Accrual Range (2) End Date of Accrual Range (3) Daily Accrual during this range (4) Profit Start Date (5) Profit End Date (6) Table of F/X Rates (for each date - one rate) This function would compare the start date of the accrual range to the profit start date, and if profit start accrual start, then start computing the summed accruals as at the profit start date, otherwise the accrual start date. For each day in this period, the F/X rates for each given day would be summed, and then averaged (as this mathematically will give me the same result as multiplying each accrual by the spot rate on that day), stopping at either the profit end date or accrual end date (which ever is earlier). The function would return this rate, which would then be multiplied by the accrual amount to get the CAD value. This is very attractive but for one reason: This is to be hosted via the web (ASP.net), and custom VBA functions CANNOT be called from the web, despite my best efforts. Thus, I must come up with another way to accomplish this, using a native access query, but I am out of ideas... Any help would REALLY be appreciated. I'm sorry for the very long message, but I wanted to make sure you understood what I was trying to do, as it is somewhat difficult to explain. Thank you so very much in advance! David |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
average of visible cells in a filtered range | dave roth | Worksheet Functions | 5 | May 23rd, 2005 12:56 PM |
Can I sum or average a range with more than 1 condition? | BobT | General Discussion | 4 | February 14th, 2005 08:28 PM |
Can you average data in 1 column based on a range of values in another? | kman24 | Worksheet Functions | 2 | November 17th, 2004 03:09 PM |
How do you average a range of cells that continually grows? | 65Stang | General Discussion | 2 | October 5th, 2004 10:55 PM |
Average 10 lowest values in range | JACKPOT | Worksheet Functions | 9 | May 24th, 2004 06:58 PM |