C
Claire Rohan via AccessMonster.com
Hi,
I am trying to solve a daily interest calculation problem. To give you an
overview of the problem - the
database is attempting to capture details of a stock broker's dealings. I
have a "client" table containing contact details for each client. I have a
"charge rates change" table which contains the commission and interest rates
for each client as they change e.g.
charge client rate date_became_current
Interest claire 0.12 21/1/06
Interest claire 0.12 23/1/06
USD Commission claire 0.04 21/1/06
I also have a "trade" table and "trade" form. A trade happens in two stages:
open and close. so in each record there is "close date," "close share price",
"close no of shares" and "open date," "open share price", "open no of shares".
the daily interest for a trade is: ((open price+close price)/2)*close no of
shares*(interest rate/365)
the problem is that the interest rate may change between the day the trade is
opened and the day it is closed. the interest is earned daily and I need to
be able to see how much was earned each month, which is problematic if the
open date and close date are in different months.
I think I know how to theoretically calculate the total interest for a trade
but I dont know how to code in access vb:
todays_rate is a double, =0
interest_today is a double, =0
i is a date
for i between [forms]![trade]![open date] and [forms]![trade]![open date] ,
i++
{
select "charge rate change" table where [charge] = "Interest" and [client]
= [forms]![trade]![client_reference]
j is a record
for j between first record and last record in [charge rate table], j++
{
todays_rate = if (date(i) is between record(j).date_became_current and
record(j+1).date_became_current,
record(j).rate, 0)
}
interest_today = (todays_rate/365)*(([forms]![trade]![open price]+
[forms]![trade]![close price])/2) *[forms]![trade]![close no of shares]
interest_to_date = interest_to_date + interest_today
}
(presumably this would be run in the trade form so can I use [Me].
[client_reference] instead of forms]![trade]![client_reference] ??)
that code would calculate the correct amount of interest for the trade but I
still have no idea how I would calculate how much of the interest was earned
in each month.
I very much appreciate any help and if anyone could give me any suggestions
that'd be fantastic.
kind regards
Claire Rohan
I am trying to solve a daily interest calculation problem. To give you an
overview of the problem - the
database is attempting to capture details of a stock broker's dealings. I
have a "client" table containing contact details for each client. I have a
"charge rates change" table which contains the commission and interest rates
for each client as they change e.g.
charge client rate date_became_current
Interest claire 0.12 21/1/06
Interest claire 0.12 23/1/06
USD Commission claire 0.04 21/1/06
I also have a "trade" table and "trade" form. A trade happens in two stages:
open and close. so in each record there is "close date," "close share price",
"close no of shares" and "open date," "open share price", "open no of shares".
the daily interest for a trade is: ((open price+close price)/2)*close no of
shares*(interest rate/365)
the problem is that the interest rate may change between the day the trade is
opened and the day it is closed. the interest is earned daily and I need to
be able to see how much was earned each month, which is problematic if the
open date and close date are in different months.
I think I know how to theoretically calculate the total interest for a trade
but I dont know how to code in access vb:
todays_rate is a double, =0
interest_today is a double, =0
i is a date
for i between [forms]![trade]![open date] and [forms]![trade]![open date] ,
i++
{
select "charge rate change" table where [charge] = "Interest" and [client]
= [forms]![trade]![client_reference]
j is a record
for j between first record and last record in [charge rate table], j++
{
todays_rate = if (date(i) is between record(j).date_became_current and
record(j+1).date_became_current,
record(j).rate, 0)
}
interest_today = (todays_rate/365)*(([forms]![trade]![open price]+
[forms]![trade]![close price])/2) *[forms]![trade]![close no of shares]
interest_to_date = interest_to_date + interest_today
}
(presumably this would be run in the trade form so can I use [Me].
[client_reference] instead of forms]![trade]![client_reference] ??)
that code would calculate the correct amount of interest for the trade but I
still have no idea how I would calculate how much of the interest was earned
in each month.
I very much appreciate any help and if anyone could give me any suggestions
that'd be fantastic.
kind regards
Claire Rohan