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
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