It might be easier to run a script to find the record you want and then do your calc. Using GetNthRecord isn't a very precise way of finding a record with a particular date.
How is the date determined? You could have the date set into a global field, create a new table occurrence of quotes, where the relationship between the two includes the globaldate = quotedate - that would give you another way of identifying the record you want.
Your model seems to make the assumption that there is only one quote on a particular day - is that a good assumption to be using?
Another option is ExecuteSQL ("SELECT the_quote_primary_key_field FROM the_quotes_table_name WHERE quote_table_date_field = ?" ; "" ; "" ; the_date )
You may have to tweak "the_date" to match the format of SQL dates
So I have found out.
The dates are inputted by the user as global fields from which a filtered table of occurrence is created. The quotes from these start and end dates are now required to calculate a rate of return.
You are correct as only the close is required for each day, however it is very easy to obtain the high and low as well as the close.
A script may be required for each equity.
A script can be triggered to run after the user has entered the dates into the global fields or give the user a button to press so that they can set the process going:
Find the start record
Set Variable $startquote from quote field
Find the end record
Set Variable $endquote from quote field
Set Variable (or set field) with calculation of rate of return based on $endquote and $startquote.
Found a Solution.
After Sorting the Quote Tables after clicking on the join icons, the "GetNthRecord(FieldName;RecordNumber" function works. Used "Count" to get the appropriate RecordNumber. I also applied the same Sorts for the Portals.
See attached file:
SubSummarizeQuotes.fmp12.zip 691.4 K