Equivalent excel function in Access

T

TarrynG

Hi

I use the following function in excel when working out the portfolio
weighted return from a number of underlying funds. I drag this function
across month-by-month so if the returns from an underlying fund are missing,
the weightfactor is adjusted for the missing return.

My portfolio return for a particular month is then
weightfact*sumproduct(alloc,returns)

Function weightfact(alloc, fund_return As Range)

Dim temp_weight
temp_weight = 1

num_funds = Application.WorksheetFunction.Count(fund_return)
num_funds = num_funds + Application.WorksheetFunction.CountIf(fund_return, "")


For i = 1 To num_funds

If fund_return(i) = "" Then

temp_weight = temp_weight - alloc(i)

End If

Next


weightfact = 1 / temp_weight

End Function

I have fairly recently moved all my data into access and have the following
tables:

tblAllocation
[FundID]
[PortfolioID]
[NRL_Date]
[Allocation]

NRL_Performance
[FundID]
[NRL_Date]
[Return]

I would like to be able to check if on a particular date there is a return
value in the performance table, if there is not, then I would like to adjuct
my 'weight factor' value accordingly.

I have been reading up about recordsets etc, but am really new to all of
this. Any help will be appreciated.

Please let me know if any of the above is unclear.

Thanks
Tarryn
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top