I have 84,530 stock exchange trades for approximately 31 stocks in which need to have an associated opening, closing and volume weighted average price (VWAP) in order to complete a transaction cost analysis.
For each trade, the first trade on that trade date for that stock needs to be recorded as the opening price and the last trade on that trade date and that stock needs to be recorded as the closing price. Additionally, the volume-weighted average price for all trades for that particular stock on that specific trade date also needs to be recorded. Therefore if a stock ABC trades 25 times on Day 1, the first trade of stock ABC on Day 1 will be recorded as the opening price and the last trade on Day 1 will be recorded as the closing prices for all 25 trades of stock ABC. Also to calculate VWAP you multiply the volume traded by the trade price for all 25 trades for ABC and divide the sum by the total volume traded.
The code needed will need to decipher and read each symbol_code and trade_date and return the respective open price, close price and VWAP so that the entire data set of 84,530 trades will be done all at once. This would be the first mfile needed.
Once an open price, close price and VWAP are associated with each of the 84,530 trades, the results from the Part 1 above will be exported to excel. From there the sample will be separated into buys and sells. Transaction costs estimates need to be calculated, and have to be done separately for buys and sells so two separate mfiles will be needed.
For the BUY mfile, I need to estimate transaction costs using the respective formulas below. The Open Price, Close Price and VWAP would all be used as benchmark prices to calculate estimated costs.
Estimated Cost=Trade Price-Open Price
Estimated Cost =Trade Price-Close Price
Estimated Cost =Trade Price-VWAP
For each trade, these three cost estimates would need to be calculated and recorded.
Once the OPCE (Open Price Cost Estimate), CPCE (Close Price Cost Estimate) and VWAPCE (VWAP Cost Estimate) have been calculated for each trade, the average of each (OPCE, CPCE and VWAPCE) needs to be calculated for each symbol_code as well as an Overall Average Cost Estimate otherwise the OACE.
The SELL mfile, would be the exactly like the BUY mfile but instead the OPCE, CPCE and VWAPCE would be calculated using the following formulas instead.
Estimated Cost=Open Price-Trade Price
Estimated Cost=Close Price-Trade Price
Estimated Cost=VWAP-Trade Price
Once the OPCE, CPCE and VWAPCE have been calculated for each trade, just like in the BUY mfile, the average of each (OPCE, CPCE and VWAPCE) needs to be calculated for each symbol_code as well as an overall average cost estimate otherwise the OACE.
Please see attached files for examples and illustrations detailing the desired results of the code.