I am creating a journal for trading purposes. In trading, Let's say I bought 1 Share of Disney at 1USD and Sold -1 Share of Disney at 2USD, Now, this is only a single trade which is shown in two rows, one shows that the share has been bought and the other one for selling. 1 means it's the entry, and -1 shows it's the exit, now I want to combine the data from the entry row and the exit row in a single row where it would show entry and exit time, entry and exit price. (average if multiple entries or exits)

This is a bit complex for me, I'd be grateful if you guys could help.

1st table is the raw data 2nd table is the desired output.

Here's the image of the table

CodePudding user response：

I'm not sure whether I understand what you need but can't you just use =average(C10:C11). If this is what you meant then you can use the same formula for all of the averages you need.

Here's an example: https://docs.google.com/spreadsheets/d/1-koCscNdEBUoBo4fcB9BoDy0RrfY3r4NpZwHoFSLpN8/edit#gid=0

CodePudding user response：

To calculate the average exit time per day use :

=average(query(A2:E6,"Select A where year(A) = "&year(A11)&" and month(A) = "&month(A11)-1&" and day(A) = "&day(A11)&" and C starts with '-' and B matches '"&D11&"'"))

To calculate the average exit price per day use :

=average(query(A2:E6,"Select D where year(A) = "&year(A11)&" and month(A) = "&month(A11)-1&" and day(A) = "&day(A11)&" and C starts with '-' and B matches '"&D11&"'"))

where 'A11' is the reference to your summary date and 'D11' is your reference to the company/stock name