MS Access: Query difference of two records to artificially create a third record

Question

This is for keeping track of market positions. Raw data is pulled from .csv files provided by brokers. Each row is a record. My issue comes into play when cross trading occurs. Ex. owning 1000, and in one order selling 2,000 shares and opening a short position. I have two records from raw data, a buy of 1,000 shares, and a sell of 2,000 shares. But a new position has been created - short 1,000 shares. So that is 3 position changes, Open of 1,000 shares, close of 1,000 shares, and open -1,000 shares.

I need a query that will recognize that 1,000 shares were bought, and that is a long/buy position, but then recognizes that a sale of anything more than 1,000 shares will close the long position, takes the difference and artificially create a new short position, even though there isn't a record of this.

All of these records fall into the transactions table. This is where the .csv files are imported to.

My query uses four columns from the transactions table to group opening and closing amounts: date, time, symbol, buy/sell. It then sums the quantity field for each group.

I only know how to query the raw data, so I only get one row of information for the scenario above. But a need a second row of information for the new position.

So instead of this:

symbol | qty opened | qty closed

xyz    |    1000    |     2000

I need this:

symbol | qty opened | qty closed

xyz    |   1000     |     1000

xyz    |  -1000     |    Null

Any guidance would be appreciated.

You don't need what you say you need. You need a query with a running sum. There's plenty of examples you can find on the Internet. — Rene
I've considered this, but I don't think it's going to work. The database needs to know when a position is closed, and a new position initiated so that it can run further analysis on the trades. If a running total can recognize every time the balance crosses 0, segregate that into its own row of information and run the rest of the query for analysis, then the running total would be great. I'll look further into it. Thanks for the direction. — Ryan Steiger

John F John F · Accepted Answer · 2018-07-09T08:15:04

I have used a running sum, assuming the ID is the order of the trades on this example:

The query to get this result is

SELECT T.ID, T.TradeSize, (SELECT SUM(TradeSize) From tblTrades TS WHERE TS.ID <= T.ID) AS [Position], IIf(Sgn([TradeSize])*Sgn([Position])<0,-[TradeSize],IIf(Abs([TradeSize])>Abs([Position]),[TradeSize]-[Position],0)) AS Closed, IIf([TradeSize]=-[Closed],0,[TradeSize]-[Closed]) AS [Open] FROM tblTrades AS T;

The logic is if the sign of the trade is opposite to the position, then the trade has closed out the trade size of the position, otherwise if the number of shares traded is greater than the position size, then we have closed the previous position, which the the trade size less the current position. You may need to play with minus signs and absolute values to get the report you want, but the basis should give you the right numbers.

EDIT: PS A better name for the Open field would be 'Opened'.

MS Access: Query difference of two records to artificially create a third record

1 Answers