如何计算股票交易的加权平均价格

Question

Given the following table of stock transactions:

TID   |DATE      |TIME    |SYMBOL|SIDE|QUANTITY |PRICE    |OPENPOSITION
339791|2014-11-14|12:45:25|ABEV3 |Buy |  -900.00|15.920000|        -900
339780|2014-11-21|10:54:37|ABEV3 |Sell|   900.00|16.650000|           0
339775|2014-11-24|14:52:59|ABEV3 |Buy | -1500.00|16.950000|       -1500
339725|2017-01-20|14:54:26|ABEV3 |Sell|  1500.00|17.280000|           0
339662|2017-02-03|10:43:31|ABEV3 |Buy | -5900.00|17.020000|       -5900
339661|2017-02-03|11:44:57|ABEV3 |Buy | -5900.00|17.229492|      -11800
339655|2017-02-03|12:37:08|ABEV3 |Sell| 10800.00|17.250000|       -1000
339528|2017-02-15|11:04:07|ABEV3 |Buy |-15000.00|17.580000|      -16000
339527|2017-02-15|12:07:30|ABEV3 |Sell|  2300.00|17.610000|      -13700
339524|2017-02-15|12:10:36|ABEV3 |Sell|   100.00|17.620000|      -13600
339522|2017-02-15|12:44:23|ABEV3 |Sell| 14900.00|17.640000|        1300
339518|2017-02-15|12:49:52|ABEV3 |Buy | -2300.00|17.670000|       -1000
339474|2017-02-17|11:45:33|ABEV3 |Buy |-20000.00|17.860000|      -21000
339472|2017-02-17|13:36:16|ABEV3 |Sell| 20000.00|17.960000|       -1000

How can I generate a mysql query to compute the avg weighted price of a transaction whether it is a buy or sell.

In the example above, the trader started buying 900 shares and selling 900 shares for a position balance of 0 (see second row). He does the same thing with 1500 shares, but then he buys and sells several times and remains with 1000 shares left. When calculated by hand, the avg weighed price of purchase is

5900*17.23+5900*17.02+2300*17.67+15000*17.58+20000*17.86/49100 = $17.59

Is there a way to build a query or php functions that considers only the prices of transactions that are still open?

Answer 1

Yes.

If that information is in a database, you'd write a sql select that would be something like:

SELECT price,quantity FROM stock_transactions WHERE ___

But hat do you mean by past closed? Are they closed if they're before the current date? (today)?

If so it'd be:

SELECT price,quantity FROM stock_transactions WHERE CURDATE() >= '2017-02-22'

That'll get those records.

Then run a result set, run through those in a while loop multiplying your price * quantity, add that (+= not assignment) to a variable (that's declared outside that loop fyi). Then repeat.