Jump to content

Need help averaging repeating fields


Recommended Posts

I'm trying to create a formula to average some fields, but am currently stumped.

I have a FMP database that I created to use as a journal for my stock trading.

I have a field set to calculate the average price of the stock based on the initial price, plus any additional stocks that I purchased after the initial purchase. The average price of the position should "average down" as I scale in more shares at a lower price, or average up if I buy higher priced shares. 

The fields related to this formula would be openingPosition (how many shares purchased),

openingPrice (cost of initial shares purchased),

scaleInPosition (number of additional shares purchased after the initial purchase - a repeating field with 10 repetitions), 

scaleInPrice (price of the new shares that were scaled in  - also a repeating field with 10 reps)

averagePrice (the calculation field to return the average price of the position after scaling in)

The closest I got to this working was:

(openingPrice + Sum ( scaleInPrice )) / (1 + Count ( scaleinPrice ))

but that only works if everything bough is 1 (1 initial share, 1 share scaled in each time, etc)

If I scale in 10 shares, it does not work.

any help or suggestions would be greatly appreciated.

Thanks.

 

Link to post
Share on other sites

This would be much simpler if you had a related table of transactions, where each purchase would be an individual record with fields for price and amount purchased. Then you could use a summary field to produce the average of price weighted by amount.

With your current structure, you will need to multiply each individual price by the corresponding amount, sum the results and divide by the total amount.

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.