locked
Converting stock buys and sells into trades RRS feed

  • Question

  • Need to convert these lists of buys and sells into offsetting trades. Would be nice to have this done through PQ! 

    The Buys and Sells are the inputs and the Trades are the required output:

    Buy   Sell Trades
    Account Stock $ Price   Account Stock $ Price Account Stock Buy $   Stock Sell $
    Jim BIDU 1500   Jim SBUX 1900 Jim TSLA 200   REGN 200
    Jim TSLA 200   Jim REGN 1100 Jim AMZN 300   REGN 300
    Jim AMZN 300   Jim TMUS 100 Jim MSFT 500   REGN 500
    Jim MSFT 500   Sally VOD 500 Jim ADBE 100   REGN 100
    Jim ADBE 600   Sally SIRI 400 Jim ADBE 500   SBUX 500
    Sally FB 800         Jim BIDU 1400   SBUX 1400
    Sally AMZN 100         Jim BIDU 100   TMUS 100
                  Sally AMZN 100   SIRI 100
                  Sally FB 300   SIRI 300
                  Sally FB 500   VOD 500
                             
    Total Buys Jim 3100   Total Sells Jim 3100 Net Trades Jim   0  
      Sally 900     Sally 900     Sally   0  

    • Edited by Ozz3287 Tuesday, July 3, 2018 4:00 AM
    Tuesday, July 3, 2018 3:59 AM

Answers

  • Hi Ozz3287,

    that's an interesting allocation. How is it called?

    I've used an iterative technique here, utilizing List.Generate:

    (BUY as table, SELL as table) as table =>
    
    let
        LG = List.Generate( () =>
    		[     IndexBuy = 0,
    			  IndexSell = 0,
    			  VolumeBuy = BUY{IndexBuy}[#"$ Price"],
    			  VolumeSell = SELL{IndexSell}[#"$ Price"],
    			  Amount = 1,
    			  RemainingBuy = 1,
    			  Counter = 0
    		],
    		each   [Amount] > 0 or [Counter]=0,
    		each [
    		Check = if [VolumeBuy] >= [VolumeSell] then 1 else 0,
    		Amount = {[VolumeBuy], [VolumeSell]}{Check},
    		StockBuy = try BUY{[IndexBuy]}[#"Stock"] otherwise BUY{IndexBuy-1}[#"Stock"] ,
    		StockSell = try SELL{[IndexSell]}[#"Stock"] otherwise SELL{IndexSell-1}[#"Stock"],
    		RemainingBuy = [VolumeBuy]-Amount,
    		VolumeBuy = try if RemainingBuy = 0 then BUY{IndexBuy}[#"$ Price"] else RemainingBuy otherwise 0,
    		IndexBuy = if RemainingBuy = 0 then [IndexBuy]+1 else [IndexBuy],
    		RemainingSell = [VolumeSell]-Amount,
    		VolumeSell = try if RemainingSell = 0 then SELL{IndexSell}[#"$ Price"] else RemainingSell otherwise 0,
    		IndexSell = if RemainingSell = 0 then [IndexSell]+1 else [IndexSell],
    		Counter = [Counter] + 1
    			]),
        
        Clean = List.Skip(LG,1),
        Expand = Table.FromRecords(Clean)
    in
        Expand

    Please check the file as well: https://1drv.ms/x/s!Av_aAl3fXRbehbIkvHxaq4K3At3F1g


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, July 3, 2018 7:17 AM