10 Replies Latest reply on Feb 6, 2011 2:35 PM by Chris

    Help With Scripts Working With Cost Averages

    Chris

      Title

      Help With Scripts Working With Cost Averages

      Post

      I'm very new to Filemaker. I'm designing a database that tracks investment transactions and accounts. I have a portal on my account layout showing every transaction. I've been able to set up, after an extensive amount of hours, weighted averages so that I can create a cost average. My first problem is not being able to do everything within the script. I had to create a field to contain the score so that I could run a 'Sum'. My second problem now lies with multiple investments. When I run Sum, it's for every transaction in that account, not differentiating which investment to create a sum for. I haven't been able to figure out how to perform a find for each Sum script I need to run for each stock. Maybe I'm going about this all wrong. 

      Example:

      Stock A Total: 175 Shares

      Stock B Total: 5 Shares

      Portal Transactions:

      Stock A | Buy | 50 Shares   | $21/share    | 6(weighted score)

      Stock A | Buy | 125 Shares | $28.09/share | 20.0625 (weighted score)

      Stock A | Buy | 100 Shares | $36.75/share | 21 (weighted score)

      Stock A | Sell | 100 Shares | $39.20/share | -22.4 (weighted score)

      Stock B | Buy | 5 Shares    | $85/share     | 85 (weighted score)

      Here's my script so far:

      Go to Layout ["Client Accounts" (Client Accounts)]

      Loop

      Go to Portal Row [Select; Next; Exit after last]

      If [Transaction::Product = "Stock A"]

      Clear [Select; Transactions::Score Average]

      Set Variable [$Weight; Value:Transactions::Qty / Client Accounts::Stock A Qty]

      Set Variable [$Score; Value:Case (Transactions::Transaction = "Buy" ; (Transactions::Debit / Transactions::Qty)*$Weight ; Transactions::Transaction="Sell" ; (Transactions::Credit/Transactions::Qty)*$Weight*-1)

      Set Field[Transactions::Score Average; $Score]

      End If

      End Loop

      Set Field [Client Accounts::Stock A Cost Average; Sum (Transactions::Score Average)]

      Exit Script []

      If anybody has a solution, please help! 

        • 1. Re: Help With Scripts Working With Cost Averages
          philmodjunk

          Hmm, have you tried using a summary field defined to compute a weighted average? I'm not sure if you can get that to work here, but if you can, no script may be needed to see the averages you have here. (A calculation field that uses getSummary may be needed to compute an average for each stock.)

          If a script is necessary, I wouldn't implement this script via a portal at all.

          Instead, switch to a layout based on the portals records and perform finds and sorts on this layout in order to work with each stock type, one type at a time for a given client.

          • 2. Re: Help With Scripts Working With Cost Averages
            Chris

            Thanks for the direction! I was trying to Perform Find and couldn't figure out why I was having so much difficulty. It seems as though portals are not easy to work with when it comes to scripting. I do need to use a script for my cost averages because every time a new transaction is created for 'Stock A' the account total for 'Stock A' changes thus changing the weight for every existing transaction before. I need to use the script to clear all the weighted scores and recalculate them based on the new total qty for 'Stock A'. Having said that, is there a way to use a script to hold the Weighted Score for each record? Then allowing me to use a function to find the sum of all the Weighted Scores? Call this be done all inside of the script?

            Example (from my original post):

            Stock A | Buy | 50 Shares   | $21/share    | 6(weighted score)

            Stock A | Buy | 125 Shares | $28.09/share | 20.0625 (weighted score)

            Stock A | Buy | 100 Shares | $36.75/share | 21 (weighted score)

            Stock A | Sell | 100 Shares | $39.20/share | -22.4 (weighted score)

            *When adding up the 'Weighted Score', I get the Cost Average of $24.66. 

            • 3. Re: Help With Scripts Working With Cost Averages
              philmodjunk

              I think I've figured a way that does not require any scripting, just summary fields.

              But first I need to ask you to check your figures in your table. When I computed the scores I got these values, which aren't exactly the same as yours:

              Buy 50   at 21      score: 5.833   = 50 * 21 / 180
              Buy 125 at 28.09  score: 19.507 = 125 * 28.09 / 180
              Buy 100 at 36.75  score: 20.417 =  100 * 36.75 / 180
              Sell 100 at 39.2   score:-21.778 =  -100 * 39.2 / 180
              Buy 5    at  85      score: 2.361   =  5 * 85 / 180

               Total score: 26.34 (scores and total score have been rounded)

               The score for the last row was quite different from yours, the others are close enough that the differences may be due to the fact that I had to use the unit price where you used the total transaction divided by the quantity.

               If those numbers show correct calculations, then the entire thing can be done without scripting as a calculation field can compute the scores and they will update with each stock transaction. A Summary field can then compute the total.

               Define a calculation cQty that includes the needed sign: If ( Transaction = "Buy" ; 1 ; -1 ) * Qty)

               Define sTotal Shares as Total of cQty

               Score is then defined as cQty * Unit Price / sTotalShares  

              • 4. Re: Help With Scripts Working With Cost Averages
                Chris

                It seems as though I rounded my numbers initially. Yes, the scores you came up with are correct. I apologize for being a Newbie; I'm not sure about your solution. I am probably missing a very simple step. 

                I'm assuming your solution is to create a calculated QTY field and a summary TOTAL field on my transactions layout so that every time I place a new transaction, they are found. I'm not quite sure how existing records from old transactions will be able to update once the new Total Qty is calculated. For weighted averages to work, every existing transaction's weighted score must be recalculated. The next problem is the summary of the weighted scores. The records need to be targeted prior to the summary. I have multiple clients, they can have multiple accounts, and can hold multiple types of Stock. All the transaction records have to be broken down to Client, Account, then Stock. Only then can the summary be found. If not, then Stock A and Stock B will have a combined cost average. 

                Again, sorry for being a Newbie. I've only been working with databases for about a month. So am I missing a very important, yet simple step? Would it help to elaborate on my entire system so far? 

                Your help is appreciated.

                • 5. Re: Help With Scripts Working With Cost Averages
                  philmodjunk

                  Because sTotalShares is a summary field, it will update automatically as you add new recors or edit existing ones. The portals relationship and any filters you set will restrict the summary field to just those records. If your portal displays stock transactions for more than one stock at a time, this method doesn't work. I thought an updated calculation that uses GetSummary would do the trick, but I can't seem to use either a portal sort order or relationship sort order to serve as the "break" field this function needs to calculate the sub total we'd need. Displaying this information in a summary report on a layout based on Transactions, BTW, won't have these issues and you can set up a sub summary part to display the weighted averages for each stock in the sub summary part.

                  Personally, to avoid confusion, I'd set up the portal with a filter to limit the transactions to one stock at a time. You can use a field with a conditional value list of that client's stock names to control which records the portal displays.

                  • 6. Re: Help With Scripts Working With Cost Averages
                    Chris

                    Thank you for helping me work through this scenario PhilModjunk! After playing around with all the ideas you gave me, I have found a solution. Let me know what you think of it. 

                    I created a script for calculating Stock A Score. I created a script for calculating the Sum of the score for that specific account then replacing field contents with "" (nothing). *For whatever reason, I was unable to put both scripts together because replace field contents would cause me to scroll through all my account. Probably better off creating little scripts and putting them together after anyways. Next, I made a new script for calculating the average by running both previous scripts. Then for Stock B, I did the same as above and created 3 more scripts. I created one script to run Stock A Cost Average & Stock B Cost Average. Now, every time I perform a transaction and click update, my account updates and all cost average scripts run. My biggest problem was Sum calculating for Stock A and B. By breaking down and clearing evidence of the score after each cost average calculation, I don't have this problem. This may have been a long way of doing it but I'm out of other ideas. Once I'm done my database, perhaps I can look at other ways of doing this. It looks something like this.

                    Stock A Score + Stock A Sum= Stock A Average

                    Stock B Score + Stock B SUm= Stock B Average

                    Stock A Average + Stock B Average = Cost Averages

                    *Luckily for me, I only have 2 types of investments I deal with for my clients. If I had more, this solutions would be time consuming and nearly impossible. 

                    It seems as though the last few days, I've found a 1000 ways how not to create the lightbulb! LOL. Thanks again for helping me test different strategies. 

                    • 7. Re: Help With Scripts Working With Cost Averages
                      philmodjunk

                      *Luckily for me, I only have 2 types of investments I deal with for my clients. If I had more, this solutions would be time consuming and nearly impossible. 

                      That's why I suggested that you NOT use scripts for this. Glad it works, but in your shoes, I would have filtered the transactions portal so that I could select stock types from a drop down and then would see only the stock transactions (and their scores) for that stock type.

                      • 8. Re: Help With Scripts Working With Cost Averages
                        Chris

                        You are right. That is a much easier way of doing it. I tried that, however, I seem to have problems working with Summary fields and could not get  them  to work with my portals.

                        • 9. Re: Help With Scripts Working With Cost Averages
                          philmodjunk

                          Are you editing data in your portal? If so you may have to add a script trigger to refresh the window when you exit the field being edited.

                          • 10. Re: Help With Scripts Working With Cost Averages
                            Chris

                            No, I wasn't editing anything but I think it may have something to do with that. I have to play around some more to learn using portals and summary fields.