Volume Weighted Average Price for FX Rates

[DumbFinanceBro]

Trying to find the average fx price for all trades done in the market (buys and sells) but noticed that sometimes when doing a vwap the average price is very different than where we actually traded. If you look at the image, we bought 890,300 dollars worth of USD/CAD at an average price of 1.354758 and sold 884,300 worth of USD/CAD at an average price of 1.35411. Formulas for those are at the top right. Problem is, average fx rate comes out to 1.44967 which is clearly very far from where we transacted in the market. Is the VWAP formula I'm using wrong or do negatives values (for sells) need to be removed from the formula entirely for it to work?

 

[alansidman]

To actually test your files, we need data in an XL2BB format or a file uploaded to a third party, ie Dropbox.com or box.net. We cannot manipulate data in a picture. There may be more suitable formula to resolve your issue, but without being able to test, well.......

 

[DumbFinanceBro]

Excel document on Google Drive link (XL2BB wasn't working for me):

Loading Google Sheets

 

[DumbFinanceBro]

To actually test your files, we need data in an XL2BB format or a file uploaded to a third party, ie Dropbox.com or box.net. We cannot manipulate data in a picture. There may be more suitable formula to resolve your issue, but without being able to test, well.......

Great idea, just uploaded the doc to google drive

 

[offthelip]

The negative values for sells will cause the calculation to go wrong unless you take the absolute values for the volume weighted price. This formula will give you the correct value:

=(SUM(R2*S2)-SUM(R3*S3))/(R2-R3)

It takes account of row 3 being negative i.e a sell

 

[DumbFinanceBro]

The negative values for sells will cause the calculation to go wrong unless you take the absolute values for the volume weighted price. This formula will give you the correct value:

=(SUM(R2*S2)-SUM(R3*S3))/(R2-R3)

It takes account of row 3 being negative i.e a sell

ok wasn't sure absolute values was the way to go but have a second opinion, thanks.