14 Replies Latest reply on Oct 26, 2010 11:19 AM by philmodjunk

    Trading database set up

    pdoak_1

      Title

      Trading database set up

      Post

      Background Info: I am using the following

      1. Filemaker pro 11
      2. Mac OS X 10.6.4
      3. Single user mode
      4. Just started using Filemaker and have constructed one database from scratch so far
      5. Basic design help needed
      6. I am modifying the contact management database
      7. Scripts - I will need them but just looking at design at the moment
      8. It will involve some calculation fields

      Essentially I want the database to record stock transaction, cash movements, dividends etc.  I thought I would use the Contact Management database starter solution as I would need to start with clients and add the trading database around it.  In addition to the contacts table, I think I will need the following tables:

      • MasterTradeDatabase - this will hold the high level trades
      • ClientAccounts - lists all accounts for each client ie GBP, EUR, etc accounts
      • ClientTrades - this will hold the allocation of the master trades relating to each client
      • Allocations - this is the allocation table which determines how the master trades will be split amongst the clients
      • CompanyMaster - this holds the stock records ie stock name, exchange, sedol etc

      The aspect that I am having trouble with is allocating the trades to each client.  I have looked at the Bonsai gardens design as this is similar in that there is an order with multiple entries for products.  This equates to my situation where there is a high level trade which then splits into smaller trades allocated to each client but how do I automate the splitting of the trades?

      Can anyone suggest the best way to get filemaker pro to split the high level trades to the various clients using a predefined allocation rule automatically.

      Many thanks

        • 1. Re: Trading database set up
          philmodjunk

          And what might that "predefined allocation rule" look like?

          • 2. Re: Trading database set up
            pdoak_1

            The allocation rule table will have the following fields:

            • allocationID
            • ClientID
            • Date
            • AllocName
            • ClientAccnt
            • AllocUnit
            • AllocTotal

            The AllocName will be the same for all clients that the allocation rule applies to.  The AllocUnit/AllocTotal is effectively the percentage that the client will receive of the total trade.

            I would like to be able to enter one trade and have filemaker Pro allocate the trade based upon the allocation rule to the vaious clients affected by the rule.

            • 3. Re: Trading database set up
              philmodjunk

              Using small numbers for simplicity...

              Then if client 1 is to buy 2 units and client 2 is to buy 3 units and client 3 is to buy 10 units, you have a "master trade" of 2 + 3 + 10 = 15 units? And AllocTotal for all 3 clients would be 15?

              If so, this is indeed very similar to an invoices, lineitems situation.

              Is each Master Trade a one time unique transaction or will it happen repeatedly with the same allocation rule?

              • 4. Re: Trading database set up
                pdoak_1

                The allocation rule will remain in place until either a client adds or removes assets from his portfolio or a new client is added - both of these situations will change the allocation rule.  I would like to be able to select the allocation rule to be used based upon the date the allocation rule is created.

                I have looked at the invoices, lineitems example but I am struggling with automating the application of the allocation rule.  In the invoices, lineitems example the equivalent to the allocation rule is selecting the product.  However, I do not want to select the allocation rule for each client but select the allocation rule to be used on all clients which have that allocation rule.  I am not sure how to automate this procedure of applying the selected allocation rule to the master trade.

                • 5. Re: Trading database set up
                  philmodjunk

                  I think you'll need a script. What I haven't seen is an answer to this question:

                  Do repeated trades take place applying the same set of allocation rule records? If so, you'll need a script that runs through the relevant allocation records for each client, generating a matching client trade record. Basically, your master trade record is an invoice, your related client trade records are line items and your script starts a new master trade record and then creates the needed client trade records from the allocation records.

                  However, I could be misinterpreting your business rules here.

                  If each Master Trade is unique with it's own set of client trade allocations, then you could simply combine your allocation and client trade tables. Before the trade goes through, the client trade records are allocations. After the trade, they are client trade transactions. The change in status is simply a case of changing a field defined in the Master Trade record from "Pending" to "Traded".

                  • 6. Re: Trading database set up
                    pdoak_1

                    The answer to your question is yes - Many different trades will use the same allocation rule.  Therefore, it seems I will need a script.

                    This will be the first time that I have written a script.  Can I just clarify the steps:

                    1. the script will invoke a layout which allows a master trade and allocation rule to be input by the user;
                    2. the script then steps through all clients with that allocation rule and creates the client trade record.

                    Is it possible to view a layout with all the proposed client trades and then select a button to commit these trades to the database.  If this is possible, please could you give me some idea how to achieve it.

                    Many thanks

                    • 7. Re: Trading database set up
                      pdoak_1

                      I have managed to write a script which takes the Master Trade and finds all clients that are connected via the allocation rule specified in the Master Trade.  However, I cannot get the fields from the Master Trade to be "copied" into the Client Trade table.  I set the fields in the client trade table to equal the relevant fields from the Master Trade via an auto calculation as I thought that this would take the current record from the Master Trade ie. the trade being input.  This worked for the first trade within the master Trade table but for all subsequent trades it just copied the first trade again. 

                      I have tried modifying the script to copy the tradeID field from the Master table record but this does not work either as the field within the Client record is blank.

                      I have attached the script below:

                      AllocateTrade

                      Set Variable [ $Type; Value:TRADEDATABASE::TranType ]

                      Set Variable [ $TradeID; Value:TRADEDATABASE::__kp_TradeID ]

                      Go to Related Record [ From table: “tradedatabase_ALLOCATIONS”; Using layout: “ALLOCATIONS” (ALLOCATIONS) ]

                      [ Show only related records ]

                      If [ $Type = "EquityCash" ]

                      Go to Record/Request/Page

                      [ First ]

                      Loop

                      Set Variable [ $ClientID; Value:ALLOCATIONS::_kf_ClientID ]

                      Go to Layout [ “tradedatabase_allocations_CLIENTTRADE” (tradedatabase_allocations_CLIENTTRADE) ]

                      New Record/Request

                      Set Field [ tradedatabase_allocations_CLIENTTRADE::_kf_ClientID; $ClientID ]

                      Commit Records/Requests

                      Go to Layout [ “ALLOCATIONS” (ALLOCATIONS) ]

                      Go to Record/Request/Page

                      [ Next; Exit after last ]

                      End Loop

                      Go to Layout [ “tradedatabase_allocations_CLIENTTRADE” (tradedatabase_allocations_CLIENTTRADE) ]

                      Else If [ $Type = "Capital" ]

                      Go to Record/Request/Page

                      [ First ]

                      Loop

                      Set Variable [ $ClientID; Value:ALLOCATIONS::_kf_ClientID ]

                      Go to Layout [ “tradedatabase_allocations_CLIENTTRADE” (tradedatabase_allocations_CLIENTTRADE) ]

                      New Record/Request

                      Set Field [ tradedatabase_allocations_CLIENTTRADE::_kf_ClientID; $ClientID ]

                      Set Field [ tradedatabase_allocations_CLIENTTRADE::_kf_TradeID; $TradeID ]

                      Commit Records/Requests

                      Go to Layout [ “ALLOCATIONS” (ALLOCATIONS) ]

                      Go to Record/Request/Page

                      [ Next; Exit after last ]

                      End Loop

                      Go to Layout [ “tradedatabase_allocations_CLIENTTRADE” (tradedatabase_allocations_CLIENTTRADE) ]

                      End If

                      The relationship that I am using is as follows:
                      Tradedatabase:allocName=allocationtable:allocName
                      alloctable:clientID = ClientTrade:ClientID
                      (I have tried to upload the image of the relationship graph but it only seems possible if you post the image to a public URL?  Is there another way of doing it.)


                      • 8. Re: Trading database set up
                        philmodjunk
                        The relationship that I am using is as follows:
                        Tradedatabase:allocName=allocationtable:allocName
                        alloctable:clientID = ClientTrade:ClientID
                          
                        Yet by your script, you have a relationship that links TradeDatabase with tradedatabase_ALLOCATIONS, not allocationtable. This is not a trivial detail so make sure you indeed have:
                         
                        tradedatabase::allocName = tradedatabase_ALLOCATIONS::alocName
                         
                        This worked for the first trade within the master Trade table but for all subsequent trades it just copied the first trade again. 
                         
                        If by "master table" you mean the tradedatabase table, I see nothing in your script that moves it off the current record in tradedatabase. If you have multiple records to process from this table, you need to put everything you've posted inside another loop that steps through these records one at a time.
                         
                        If you don't already own a copy of FileMaker advanced, I strongly recommend you purchase a copy. With FileMaker advanced, you can enable a script debugger and then step through a script like this one step at a time while watching the values in fields and variables change. It can be very instructive when you are learning scripting and can help you catch your mistakes as well.
                         
                        Two design notes:
                        Go To Related Records doesn't have the best safety net in the world for trapping errors that might occur. In particular, if you have no related records, the script stays on your current layout, but continues to execute without even displaying an error message unless a subsequent script step fails due to being on the wrong record. Given that subsequent script steps create new records, this could create chaos in your database. To be safe, you should include a test for related records before using GTRR to jump to a different set of records on aother layout. If [Not IsEmpty ( RelatedTableOccurrence::KeyField ) ] is usually the best way to test for this.
                         
                        For more on GTRR, you might check out this article on the subject:  The Complete Go To Related Record
                         
                        Basing a relationship on name fields rather than a serial number field sets you up for problems down the road. If you mis-enter an allocation name, create some related records and then discover and correct your mistake, the related records then seem to disappear as they are no longer linked to the parent record once you change the name in your parent record.
                        (I have tried to upload the image of the relationship graph but it only seems possible if you post the image to a public URL?  Is there another way of doing it.)
                        You can also click Edit on your original post and upload an image to your original message at the start of this thread.
                        • 9. Re: Trading database set up
                          pdoak_1

                          Thank you for the advice.  I made the changed that you suggested and everything now works as expected.  

                          I now have another problem though.  I have a pricing table which shows the closing stock prices for each day.  I would like to be able to set a date and then have the closing price for that day inserted into the trade database so that I can calculate the profit and loss and market value of each position.  In order to do this, I have set up a table with a global variable which just holds the reference date and have a relationship between the pricing table and the trade database table which looks like this:

                          tradedatabase: SEDOL = pricingtable:SEDOL and

                          tradedatabase:refdate = pricingtable:date

                          The tradedatabase:refdate is set to auto calculate so that it updates to the date set in the global variable table.

                          When I change the date in the global variable table and then go to the tradedatabase table and refresh the window, the price and all the calculated fields update as expected.  However, I have a layout which is based upon the tradedatabase table and uses summary fields to group by client but this does not update at all when the window is refreshed.  Should this work in the way that I am expecting or is there another way to achieve the same result.

                          • 10. Re: Trading database set up
                            philmodjunk

                            I really can't tell from here.

                            What isn't updating, the prices?

                            Do your summary fields summarize the same "calculated fields" that update in your first instance?

                            Presumably, those "calculated fields" are fields of type "Calculation" not number with an auto-enter calculation?

                            • 11. Re: Trading database set up
                              pdoak_1

                              Sorry, I probably did not explain myself clearly.  When I change the date in the global variable table and then refresh the layout which looks up the price for that day and then uses it to calculate the profit and loss, all the calculated fields update as expected.  However, within that table, I have summary fields which are based upon calculated fields and these summary fields only update once and refresh the window and resort the table based upon ClientID.  In addition, the layout has a grand total part which has the summary fields placed in it but this does not seem to update at all.  

                              What am I doing wrong?

                              • 12. Re: Trading database set up
                                philmodjunk

                                You might try this script step: Refresh Window [Flush cached Join Results]

                                • 13. Re: Trading database set up
                                  pdoak_1

                                  The script that you suggested updated all the individual fields but it still does not update the summary calculation fields in the layout.  However, when I sort the layout by ClientID that updates the summary calculation fields.  Should it work the way that I am expecting or not?

                                  • 14. Re: Trading database set up
                                    philmodjunk

                                    I'm not quite sure what you are expecting. Here's some info that may clarify the issue for you:

                                    Summary fields compute totals based on the current records in the found set. If you perform a find, omit records or do some other action that changes the records that are part of this set, you get different values in your summary field. If your summary field is in a sub summary part or you have a calculation field that uses getSummary, the system will compute a sub total based on the records in the found set and the specified sort order. Sorting your records differently, could then change the values returned in a summary field or a calculation field that uses a value from a summary field.