I got this far.......

[Dwight]

I am calculating the performance of client portfolio accounts.

I have a query that compares Day1 Port Value to Day0 portfolio value and calculates the percentage gain or loss. This was accomplished with a self-join and and ID field that counts up. For example Day1 = $11 and Day0 = $10 then the gain is 10%. This continues for each day (Day2 vs. Day1 etc.) the account exists so that I end up with a column of daily percent gains or losses.

Now I would like to create a client performance index that begins at $100 and tracks the account's performance for the specified time period. The problem that I am running into is that the percentages must be compounded, not simply summed, and the next value does not exist yet as it did with my portfolio values so I can't get the self-join trick to work.

It should be $100 * (1 + 10%) = $110
and then 110 * (1 + X%) +$$$$

I would really like to do this in a query, not a report.

If anyone could point me in the right direction I would be grateful.

 

[neileg]

Wood and trees

You are deriving your percentages from the base performance data. You want to base your index on these percentages and you are finding it hard. So logic suggests you should go back to your base data.

An index is simply a way of elimiating the absolute values of your data and making them relative.

So if your portfolio value on the day you want to start your index (day 0) is $1,232.89 and the value on the day you want to report the index is $1,945.88, then the index formula is
(1232.89/1945.88)*100

Now this won't work if the portfolio has a funds injection or a withdrawal, but then neither does your percentage growth calculations.

 

[Dwight]

Thanks for the analysis. Very perceptive comments. You hit the core issue dead on when you said that an index is only a way to make things relative rather than absolute. Ultimately the Index performance will be used to calcluate the variance of the portfolio's returns. That way I can compare it to a benchmark and determine if it experienced higher or lower risk. Cash flows are taken into account by the query before the daily percent changes are calculated. I left it out because I knew how to do this.

If I can just get this Index column sorted then I will have a cash flow neutral time weighted return column that can be used for varying time periods to measure risk and return.

I think my head is screwed on straight about this but let me know if it is not.

Dwight

 

[neileg]

Two points
Firstly, I got my formula upside down! Should have been (1945.88/1232.89)*100

Secondly, I am now unclear what it is you want. I was suggesting that you don't derive the index from the percentage returns, but rather from the true movement in the portfolio value between your index day 0 and the date you want to calculate the index. You will, as you obviously understand, have to adjust for the in and out cashflows.

On reflection, it is the compensation for the cashflows that makes this difficult. By calculating the percentage on a daily basis you can deal with this simply. Trying to adjust over a long period presents other problems.

Perhaps the most practical way to do it is to calculate the index daily as part of the routine to calculate the percentages.

 

[Dwight]

The last sentence of your post is exactly what I am trying to do. I just can not get it done. I had another post called "Another Previous Record Query" that has a sample database of what this is all about. Originally, I thought getting the daily percentages would be the hard part and then once I had those creating the Index would be gravy. But my reality has differed from my perception. Fire away if you have any more questions.

Don't worry about the cash flows, those are taken care. And taking a holding period return between two dates will give me the total return but you can not calculate variance from two data points.

Dwight

 

[neileg]

I don't understand why you started this as a new thread. The mechanism JonK spelled out for you getting a value from the previous record is all you need.

The index for the current record is the previous index * 1+%change. Obviously the index for the first day is 1. The logic is just the same.

While I'm happy to help, I think you need to deconstruct JonK's approach and add in your index calc on the same lines as part of the same set of queries.

 

[KKilfoil]

Here's my two cents...

You seem to already have a query that returns a field for Daily Percent Gains/Losses (call it DPGL).

I'm assuming your query contains a ClientID, and a date field (call it TheDate)


I think you need a vba public FUNCTION that takes three arguments (ThisClient,StartDate,EndDate) and calculates the cumulative geometric growth, returning the result as a Single or Double precision variable.

In your function, create a recordset using a string like:

"Select * from qryYourQuery where ClientID = ThisClient and TheDate is between StartDate and EndDate"

(note that this is NOT proper syntax!)

Once you have the recordset, loop through the records using .movefirst, .movenext, etc

If you initialize a variable (single or double) to 1.0, you multiply this variable by (1+DPGL) each time through the loop.

Once you are out of the loop, set the Function name to the variable that's been accumulating index multiplications, and you're done.


If you don't have much vba experience, I'm sure someone here can give you a few pointers.

 

[Dwight]

Refer to the timeline of events as to why I started a new thread. My second post was before John's answer. His post was a nice suprise providing a solution to a problem I had already solved. I ran into a new problem and hence the new post. Next time I will just keep the same thread going if that is proper ettiquette.

On the surface I thought the logic was the same too. But as my first post describes I do not actually think it is. In one instance you have the next account value and you are simply bringing it back a period and now I have to generate the number and then bring it pack a period. I just can not get it to work with the counter method. Maybe it is me.

The loop method sounds promising. I do not know VBA but I guess I soon will. It looks like heavy duty stuff.

Thanks for the tip.

 

[Dwight]

After more thought I think I can get it to work with Jon's method. I think I need to create another subquery. Jon built a database called Previous Total where he accomplished something similar to what I want. I can not locate the thread but I have the database at home. I have been adapting his SQL but I always get blank fields.

I can see know that it would have been better to keep one thread too.

I will keep plugging away.

Dwight

 

[Dwight]

After many hours of plugging away my head is plugged but the database is sill one up on me. I am sure I have gotten close but that does not seem to cut it in the database game.

I am sure that a database created by Jon K is doing what I want my database to do. I have tried to adapt his method to mine but to no avail. He created a column that carries a previous running sum. I believe this is analogous to my running index problem. But when I try to adapt his SQL queries I either get blank fields or it wants to return more than one record which is obviously not allowed. I am stuck so I thought it was time to ask for more help.

I have attached (I hope) the database (97 format). Any help would be appreciated.

 

[Dwight]

I could also do this in a report. I just read a post by VJMEHRA called running sum where it was suggested that he do his calculations in a report. How would I do this?

I studied a Visual Basic book at the library as well. I think since I have a column that counts for each unique client and a column of percentages that a loop program could indeed do the calculations. But loops were on page 800 of 1300 in this book and the only thing I know about VB is that I should learn it.

Thanks,

 

[Dwight]

My database now calcalculates a compounding value. What I had to end up doing was saving the daily percent changes to a table and then use them in a Self Join query. I was really hoping to avoid storing these calculated values as this is a big no no in databases but I could not get it to work any other way.

Thanks for everyone's comments on this topic.

 

[neileg]

Please you got an answer. Clever and elegeant code is great, but sometimes hacking gets you there as well!