cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stockturner Frequent Visitor
Frequent Visitor

Multiline text import using query editor - variable number of rows.

 

Hi,

I have some trade data that comes in from my broker that can contain multiple lines related to one trade.   See example below.

 

  • The data is for actual trades in a brokerage account. 
  • The account is used for both stock trades and options trades.
  • The options trades sometimes have complex orders which show up on multiple lines.
  • The headings are shown in the first row. 
  • I need to be able to import and clean up the data in the query editor.

Comments about the data.

  • The trades at 12:34pm and 12:29 pm are simple purchases of stock.
  • The trade at 12:28 pm was part of a complex trade that involved multiple options trades that are shown on the net few rows.  These are all related records which I need to bring in.   I also need to add the date and time next to each related record so I know it is part of the same transaction.
  •  The next trade was at 8:40 am.  
    • This starts a new trade.
    • I know that it is a complex trade because there are multiple lines.   
    • The instance where a date and time appear would mark the start of the next transaction.
    • Any given new trade will have at a minimum only 1 line, and in the case of a complex trade, it will have multiple lines.   The number of lines will vary dependent on the type of trade that was done.

 

For each trade I want the table to show the fields from row 1.   When there is a multiline trade, then I want each line to have the date and time shown, so I get a nice clean table.

 

 
 
Notes Time Placed Spread Side Qty Pos Effect Symbol Exp Strike Type PRICE TIF Status
3/19/2018 12:34 STOCK BUY 5 TO OPEN DIS STOCK 101.2599 101.26 LMT
3/19/2018 12:29 STOCK BUY 2 TO OPEN AMZN STOCK 1530.15 1530.15 LMT
3/19/2018 12:28 CUSTOM BUY 1 TO CLOSE AMZN 6-Apr-18 1572.5 PUT 60.5 -28.22 LMT
SELL -2 TO CLOSE AMZN 6-Apr-18 1570 PUT 58.84 CREDIT
SELL -1 TO OPEN AMZN 6-Apr-18 1532.5 PUT 37.76
BUY 2 TO OPEN AMZN 6-Apr-18 1522.5 PUT 33.36
3/26/2018 8:43 CUSTOM SELL -6 TO OPEN BA 6-Apr-18 332.5 PUT -4.43 LMT DAY FILLED
BUY 12 TO OPEN BA 6-Apr-18 330 PUT <empty>
BUY 6 TO CLOSE BA 29-Mar-18 325 PUT <empty>
SELL -12 TO CLOSE BA 29-Mar-18 322.5 PUT <empty>
3/26/2018 8:40 CUSTOM SELL -1 TO OPEN AMZN 6-Apr-18 1535 PUT -11.7 LMT DAY FILLED
BUY 2 TO OPEN AMZN 6-Apr-18 1532.5 PUT <empty>
BUY 1 TO CLOSE AMZN 6-Apr-18 1502.5 PUT <empty>
SELL -2 TO CLOSE AMZN 6-Apr-18 1500 PUT <empty>

 

 

Thank you for your time.

John

 

1 ACCEPTED SOLUTION

Accepted Solutions
stockturner Frequent Visitor
Frequent Visitor

Re: Multiline text import using query editor - variable number of rows.

Thanks for the suggestion.  The info was helpful to provide me with additional ideas and background.    

 

While I was not able to develop a specific solution to this issue, I am able to solve the problem a different way.   I went back and found a much cleaner data source that has one row per security, with each row containing the description in a separate field.   This let me split the table for the description into the component parts,  (underlying, expiration date, strike price, and call or put)

 

The only disadvantage is that this alternative data source is batch updated overnight, rather than in real time after the trade.   However, for my purposes this is sufficient.

  

Thanks

John

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Multiline text import using query editor - variable number of rows.

@stockturner, You may take a good look at https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data and do research into The Advanced Editor.
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
stockturner Frequent Visitor
Frequent Visitor

Re: Multiline text import using query editor - variable number of rows.

Thanks for the suggestion.  The info was helpful to provide me with additional ideas and background.    

 

While I was not able to develop a specific solution to this issue, I am able to solve the problem a different way.   I went back and found a much cleaner data source that has one row per security, with each row containing the description in a separate field.   This let me split the table for the description into the component parts,  (underlying, expiration date, strike price, and call or put)

 

The only disadvantage is that this alternative data source is batch updated overnight, rather than in real time after the trade.   However, for my purposes this is sufficient.

  

Thanks

John