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?

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?

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.

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.

I need an elegant solution for appending sequential serial numbers to parent file ids in a child file. Scenario: I am trying to calculate investment performance for multiple investment portfolios. I have two files: a portfolios.fp5 (parent) and a performance.fp5 (child). The performance file keeps monthly performance data and has cumultive performance calculations, requiring me to use a self-join so that I can use data from previous records (ie., 1 month back, 6 months back, 120 months back, etc.). This is no problem really. The problem arises when I attempt try to track more than one portfolio in the file. I can track the performance of one portfolio in one file and calculate cumulative and average annual performance by geometrically linking previous records' monthly performance. I go back 120+ records; however, sequential serial numbers are critical to the problem. Alternatively, since the performance records all have month end dates (report_date), I could also link records by using Date(Month(report_date), 1, Year(report_date) ) -1. Either way, the problem occurs when I reference another portfolio in the performance file, then my sequential numbering gets screwed up. I suppose I'd like my performance file to serializes itself using the portfolio_id from a separate portfolio file. For instance: Portfolio_ID & NumToText(Serial) But it must be in sequential order so that the performance calculations work. Again, a solution with a date might help too. Please, someone, help. Thanks.