AnsweredAssumed Answered

Help With Scripts Working With Cost Averages

Question asked by Chris on Jan 30, 2011
Latest reply on Feb 6, 2011 by 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! 

Outcomes