How to combine data from multiple rows into a single row based on numbers and certain texts in Google Sheets?

Question

I am creating a journal for trading purposes. In trading, Let's say I bought +1 Share of Disney at $1 and Sold -1 Share of Disney at $2. Now this trade will be shown in two different rows, one row represents that 1 share of Disney has been bought at $1 and the next row shows the trade exit means -1 Share of Disney at $2, now I want to combine the data from the Trade entry row and the Trade exit row in a single row so that it shows both the entry price and exit price with their respective times in a single row.

I need the desired row to show entry time, entry price and exit time and exit price from different rows. Also, the time and the price need to be averaged in case of 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.

Answer 1

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

Answer 2

After review and using a query/pivot, I have to following output:

I had to prepare the source data

• by formatting the time to a number
• and also the position to a number
• by adding a column to identify some sort of OrderID, to link buy and sell activities to each other, so it can be used for grouping in the pivot. The way I did it, isn't totally correct as when you buy extra before you sell all, there would be an issue.
• Also I added a column to identity whether it's a buy or sell to use as a pivot source. It probably can be done within the query as well.

Also an extra thing to take in mind is when you sell more then 1 in one time, this should probably weight more in the averages. So there should actually be a weighted average be calculated, instead of just the average.

This is my sample sheet

This is the query

=query(DATA!A2:G, "select F, A, C, avg(B), avg(E), max(D)
  where A is not null
  group by F, A, C
  pivot G
  order by A
  label F 'OrderID',
        A 'Date',
        C 'Company',
        avg(B) 'Time',
        avg(E) 'Price',
        max(D)  'Size'
  format avg(B) 'HH:MM:SS'
")

---

initial answer: 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

Answer 3

Nested ObservableObjects don't work in SwiftUI unless you manually propagate the state back up to the parent object by calling objectWillChange.

Generally, the standard route is to use a struct for your model and then have the array managed by the ObservableObject parent.

If you change your model to the following, you'll see the drag start to update:

struct Node: Decodable {
    public let id: Int
    var x: Double
    var y: Double
    public let text: String
    public var red: Double
    public var green: Double
    public var blue: Double
    public var shape: Int
}

Note: you'll also need a little more logic for your dragging, but that's beyond the direct scope of this question -- you'll see the item jump at the beginning of the drag because you need to store the initial translation