Creating weekly net change fields for stock quotations
I have a stock program to analyze performance potential of about 700+ stocks that changes on a daily basis, not only regarding the data, but the listing of stocks gets modified every day through addition of news and deletion of old ones. Currently, I have a field 'set price' that is the reference field for price changes on a daily basis. I import current prices into a field 'closing price' to allow the other fields to help evaluate certain metrics for my analysis. What I want to do is to do a weekly net change calculation to add a momentum factor into my analysis.
Example: Stock XYZ gets entered to the data base. 'Set price' now has a value of $20.00. When I do the import into 'closing price' the next day, it will show a value of $21.00. One of the calculations would show the net change between 'closing price' and 'set price' of +$1.00. Here is what I need. I want to freeze the price on each Friday and compare that number to the prices from the previous Friday. To me this presents an obvious problem with transferring the prices from those 700+ records from one field to the other each Friday. It's easy to import the new numbers into a field, let's say 'closing current friday', but what can I do to copy all the values from this hypothetical 'closing current friday' field into the hypothetical 'closing previous friday' field. I will have to do this on a weekly basis. I do not need to archive any of these numbers once they get deleted.
I've been using FileMaker since it first came out and I'm using version 11 on a Mac now. I am running into a complete block on how to do this, and thinking that it is a simple process.
Seems like you need to import this data once a week into a related table where a date field identifies the week and a stockID field identifies the stock. THen you can use a relationship to refer to the record of the same stock from the previous week.