Calculation from a portal or relationship

[James_bk]

I have a database that uses a relationship to track changes to daily prices of stocks. Is there any way to perform calculations on what I am seeing in the portal or through the realtionship?

I have the realtionship set up as well as the portal but cant wrap my mind arround how to calculate the percent change from one day to the next. I can see the changes day to day in the portal but I cant access via a calculation. I know Im almost there but I have hit a road block. Thanks

portal_problem.zip

[BobWeaver]

The kind of calculations that you would normally do on records that you are viewing through a portal, are totals, averages, max and min, and you would use aggregate functions to do them. Calculations between adjacent records (like daily percent change in price) is trickier because databases inherently have no order between records until you sort them or you set up a relationship between them based on some criteria such as date and/or stock symbol. So, this isn't going to be as simple as on a spreadsheet.

To calculate the percent change in a stock from one day to the next, you would make a calculated field in the same table as where the daily stock price records reside. You need to set up a special selfjoin relationship based on stock symbol and date, to retrieve the previous day's price

If your table with the daily price records is called Stock_Data, and your selfjoin table occurrence is called Selfjoin, then the relationship is like this:

Stock_Data::Date > selfjoin::Date

and

Stock_Data::StockSymbol = selfjoin: StockSymbol

You also need to sort the Selfjoin table side of the relationship by Date descending.

Now, when you access the stock price via that relationship it will get you the previous day's price for the stock. You can then calculate the percent change.

See the attached example.

Portfolio.fp7.zip

Edited April 17, 2006 by Guest

[James_bk]

Ok thanks, that makes alot of sense. Im still wondering if there is a way to take the daily changes in percent and map them so that the database can determine a trend up or down?

I see that I can sort the records in the portal to show the change from largest to smallest but can I perform calculations on this? I dont really want to see the change I was to be able to have the database tell me if the stock has been going down every day this week or not. Thanks

[BobWeaver]

You can set up another selfjoin relationship that groups stocks by the week of year, or 7 calendar day range, and counts the number of days that the percent change is positive or negative in that group. I've modified my previous example to include both of these methods. See the new attachment below.

Portfolio.fp7.zip