A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date Range and Average F/X Rate



 
 
Thread Tools Display Modes
  #1  
Old June 23rd, 2005, 02:26 PM
David
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 09:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.