Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Recommended Posts

I have a Stock Trading application where I have a WATCHLIST table that refers to the current stock price (updated daily) and other data in related tables.

When the price is updated each day, the superseded data gets archived in a Stk_Price_Archive table. I'm trying to calculate a moving average of the price of each stock for the previous 7 days from the archive table.

I then want to reference that Average in my WATCHLIST table to compare with the current price to identify any significant move.


I've tried creating a Calc field in the Watchlist table using several date comparisons, none of which worked.

My latest attempt was to create a Calc field (Text) called Flag_7_Day in the Stk_Price_Archive table which is set to "Yes" if the Stock Price Date is in the last 7 days.


I then tried to create a Calc Field (Number) called Avg_Price_7_Days in the Watchlist table defined as follows:


0 + ExecuteSQL ( "SELECT AVG(S.Market Price)

FROM WATCH_Stk_Price_Archive S

JOIN Watchlist W ON W.Stock_Code = S.Stock_Code


; "" ; "" )


This just returns a "?" - can anyone identify the problem or suggest a better way?

Link to comment
Share on other sites

If I had to guess, I'd say that your SQL query is giving you a "?" because you have S.Market Price instead of S."Market Price". It's a common practice among FileMaker developers with FileMaker Pro Advanced to use custom functions (like this and this) to quote any references to field names to avoid issues like that, and to avoid problems when fields get renamed but references in SQL queries don't get automatically updated.


Even if that resolves the syntax issue, your SQL query won't quite get you what you're looking for, and it could be slow. It won't get you what you want because what you have would return the average for all Stk_Price_Archive records in the last 7 days (maybe, depending on how Flag_7_day is calculated) with any related Watchlist record, not just the Stk_Price_Archive records related to a particular Watchlist record. It could be slow because (1) as I said, it's averaging more data than you want, (2) it's doing a join it doesn't have to, and (3) it may be re-calculating the Flag_7_Day field for your entire archive history to resolve the query. Consider instead:


Let ( [

_7DaysAgo = Get ( CurrentDate ) - 7 ;

_query = "SELECT AVG ( \"Market Price\" ) FROM Stk_Price_Archive WHERE Stock_Code = ? and Stock_Date >= ?"

// the SQL custom functions I mentioned are omitted for clarity

] ;

ExecuteSQL ( _query ; "" ; "" ; Watchlist::Stock_Code ; _7DaysAgo ) // extra parameters correspond to "?" in the query



This doesn't use a join, constraints to only the Stk_Price_Archive records for one WatchList, and doesn't call for a calculated field in Stk_Price_Archive.


Another approach would be to update the moving average at the Watchlist when the price is updated without referencing the archived data, such as with this custom function. Note that this would be a contiguous, exponentially weighted moving average, not an equal-weight sliding window average like you started with, so the exact numbers you get will be different.

Link to comment
Share on other sites

Thanks jbante, the quotes around Market Price did not fix the problem, but I'll have a look at your other suggestions and give them a go.

Appreciate your time



Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use