geometicallly linked data through self-join?

[abp]

I'm new to FMP and have been struggling for days. Someone please help me. It looks pretty easy to calculate summary data using a self-join; however, if I try to multiplying data across sequential records to get a cumulative result over time, I get lost. Here what I'm trying to do:

I have 5 different investment portfolios (portfolios.fp5), each has its own monthly performance history (performance.fp5), creating a one-to-many relationship.

In the peformance.fp5 file, I'm attempting to calculate cumulative performance by keeping a running index which starts at 100. In other words, the index = 100 and if the first record shows a 5% return, then the new index is 105. If the third record shows a 10% return, then the new index is 115.5 (105 * (1 + 10%)), and so on.

I run into three problems I can't tackle. First, I don't know how to make sure the first record in performance.fp5 starts at 100. Second, the calculation for the new index is based on the previous index value (through a self-join); thus, I always get a circular reference warning. Finally, I don't know how to tell the performance.fp5 database to distinguish between portfolios.

This stuff is a no-brainer in a spreadsheet, but I'm really struggling in FMP. Can someone please offer me some direction on any of these road blocks? Thanks.

[falkaholic]

If I get what your tring to do, this should help point you in the right direction...

To calculate a portfolio total, you would make the relation (call it performace_link) to make your one-to-many.

In in portfolios.fp5 you can make a calculation field that does this.

sum(performace_link::Totalworth)

That will give you the sum of a field called totalworth that relates to the portfolio.

If you want something better then just the sum. Make a calculation field in the performace.fp5 that does what you need, the do a sum on that.

HTH

[abp]

Thanks for replying. It sounds like you solved my third problem by eliminating the self-join and placing the calculation in the portfolios.fp5 file.

But it seems like I'd still have a problem grabbing specific periods. For instance, if I just want the last three months, I was looking for serial-1, serial-2 and serial-3. Should I be looking for dates instead? My calculations always require the most recent monthly performance value.

Thanks again for thinking this through with me.

[LiveOak]

Why not just divide the entry by the initial investment to get the index? You don't need to process the monthly incremental percentages to get the monthly index, in fact, doing so will lead to every increasing errors in its calculation.

The structure you are trying to create is a linked list. This is something that FM doesn't do at all well, even though there are ways to set it up.

-bd

[abp]

Thanks for the feedback. In my case, I don't really have initial investments or portfolio values. I only created the index in order to make performance calculations: (ending_value - beginning_value) / beginning_value

Alternatively (and preferrably), I would calculate performance by geometrically linking returns. Example: (1 + return_month_1) * (1 + return _month_2) * (1+ return_month_3) - 1. This method drove me banannas in FM especially when trying to distinguish between portfolios; that's why I'm trying to create an index.

The linked list you mention sounds like my solution. Why doesn't FM handle it well and how can I set it up?

[LiveOak]

Interesting concept, no initial investment or portfolio value (), must be the next concept in MLM.

Lookup the initial value from one record to the next. To do this either enter or calculate a previous record ID to use in the relationship. If each investment is uniquely identified, you can relate common sets by this number and use the Max(relationship::RecordID) to get the last ID.

FileMaker doesn't handle linked list structures well (nor does any normal database program) because the calculations won't automatically trigger all the way through the list. These types of structures are almost always handled with programs designed for this purpose. This is NOT your typical linear database structure. This isn't speculation on my part, I've actually written one using a mirror file to get around the circular reference problem.

-bd

[abp]

Thanks. You're funny. Insightful too.

The Max() function sounds good, but at what point does it get bogged down in practice. If I understand Max() correctly, it will look at every record each time. One hundred portfolios with 120 months of data will create 12,000 records. How will Max() hold up under those conditions?

[BobWeaver]

Actually, this might not be a bad application for a multi level database, since the past month's records will become static and won't have to be looked up after their initial performance calculation. I would set it up with the following fields:

IDportfolio - text - Identifies the particular portfolio

RetYear - text - Identifies the year that the performance pertains to

RetMonth - text - Identifies the month that the performance pertains to

Key - A calculated field that is the concatenation of the above 3 fields. This will be unique because you can only have one performance value for a given portfolio in a particular month and year.

Formula =IDPortfolio & RetYear & Right("00"&RetMonth,2)

CurrentPerformance - number - the current month's performance for the portfolio (not the cumulative performance)

MatchPrevKey - A calculated field that links to the previous month's performance record for the portfolio

Formula = IDPortfolio & Year(Date(RetMonth-1,1,RetYear) ) & Right("00"& Month(Date(RetMonth-1,1,RetYear)),2)

Then set up a relationship called "Previous" within your file with MatchPrevKey on the left side, and Key on the right.

Then create two more fields:

PreviousPerformance - Number field

CumulativePerformance - calculated field with this formula:

Formula = Case(IsEmpty(PreviousPerformance),1,PreviousPerformance) * (1+CurrentPerformance)

Now go back to the field definition options for the field PreviousPerformance and set it to Lookup based on the above relationship looking up from field CumulativePerformance. Even though this is technically a circular relationship, Filemaker will let you do it.

When you first set this up, you may have several month's worth of data, so you will need to force filemaker to do enough relookups to propagate the data through all records. You have to do n-1 relookups where n is the number of months of data that you have. So, if you have 20 months of data for portfolio X, then you need to do 19 relookups to get the data propagated. Note that you don't have to do 19 relookups for every portfolio. Just enter all the data, figure out which portfolio has the most records, and do that many relookups. Doing more than the minimum number won't hurt anything. Once the initial data is in the file, adding the new data for each month should automatically lookup the right data.

I have included a sample file that should get you started.

PortPerf.fp5.zip