5 Replies Latest reply on Nov 12, 2013 10:10 AM by philmodjunk

    Stock Analysis

    eekamouse

      Title

      Stock Analysis

      Post

           Hi I have built up a database of stock data which I update daily with stock prices per the following fields

           Stock Symbol

           Date

           $Value Traded (calculation of number of shares traded*closing price)

            

           Is there a way to display a report that display a list sorted by the symbol that can show the average dollar value traded for the last 7, 30, 45, 60 days and so on.

            

           Brain is hurting from trying to tackle this and would appreciate any help.

            

           Anthony

        • 1. Re: Stock Analysis
          philmodjunk

               Either Relationships or calculations using ExecuteSQL can reference records with the same stock symbol for a computed range of dates for the last 7, 30, etc time intervals in order to return an average of the ValueTraded field.

               Do you want to try this with an SQL query or self join relationships created in Manage | Database | Relationships?

               Do you just need to display the average values or will you need to use these values in additional calculations?

          • 2. Re: Stock Analysis
            eekamouse

                 Thanks for answering

                 I would like to try both if possible but I am only familiar with the self join. Would I need SQL installed or is it just the syntax I would be using and which I have a bit of familiarity with?

                  

                 Just the average values would be needed.

                  

                 Thanks again

            • 3. Re: Stock Analysis
              philmodjunk

                   It would take more time to describe both methods than I am willing to spend doing so. And thus I am asking your to choose.

                   ExecuteSQL() is a built in function in FileMaker 12. It is not available in earlier versions. While powerful, this function can be very difficult to use due to very uninformative error results when you get the SQL syntax wrong, it is best used in the current version by those who already have a basic knowledge of SQL queries.

              • 4. Re: Stock Analysis
                eekamouse

                     Then self join please 

                • 5. Re: Stock Analysis
                  philmodjunk

                       Set up a self join relationship:

                       StockData----<StockData|SameSymbol

                       StockData::Stock Symbol = StockData|SameSymbol::Stock Symbol

                       StockData and StockData|SameSymbol are both Tutorial: What are Table Occurrences? with the same data source table.

                       Define a summary field in StockData, sAvgValue, that returns the average of your value traded field.

                       On your StockData layout, put a one row portal to StockData|SameSymbol. define this portal filter:

                       StockData|SameSymbol::Date > Get ( CurrentDate ) - 7

                       Put sAVGValue in the portal row and it should show the average over the preceding 7 days.

                       Repeat this process but, using -30, -45, -60  in the portal filter to get totals for other time intervals.

                       You can also use a global field instead of an explicit number and then you can get different totals for any time interval the user selects, but then you should use this relationship:

                       StockData::Stock Symbol = StockData|SameSymbol::Stock Symbol AND
                       StockData::gGlobalField X StockData|SameSymbol::anyfield

                       so that the portal filter will automatically update whenever the value of gGlobalField is changed.

                       Please note that these values are display only. The values cannot be accessed by a script or calculation. A different approach can be used if such is needed.