Skip to Main Content
  • Questions

  • Buy/ Sell mapping by first in first out

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: December 19, 2015 - 2:53 am UTC

Last updated: December 21, 2015 - 11:54 pm UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

Hi Tom,

I would like to map Buy/sell trades by FIFO.
i.e, when a sell trade try to map buy trades, it has to map the lowest transID available in the buy table.
for example, I have the following sample data,

create table Sell
(
ticker varchar(5),
qty int,
price float,
transId int
)

create table Buy
(
ticker varchar(5),
qty int,
price float,
transId int
)

insert into Sell values ( 'ORCL', 1000, 34.5, 101)
insert into Sell values ( 'ORCL', 200, 35.0, 102)
insert into Sell values ( 'ORCL', 600, 35.5, 103)
insert into Sell values ( 'MSFT', 500, 44.5, 111)
insert into Sell values ( 'MSFT', 500, 45.5, 112)

insert into Buy values ( 'ORCL', 200, 32.5,1 )
insert into Buy values ( 'ORCL', 300, 33.5,2 )
insert into Buy values ( 'ORCL', 800, 34.5,3 )
insert into Buy values ( 'ORCL', 700, 35.5,4 )

insert into Buy values ( 'MSFT', 200, 44.5,11 )
insert into Buy values ( 'MSFT', 400, 45.5,12 )
insert into Buy values ( 'MSFT', 100, 44.5,13 )
insert into Buy values ( 'MSFT', 300, 45.5,14 )

And the following is what I expect:

ticker sell_transID sell_qty buy_qty buy_transId
ORCL 101 1000 200 1
ORCL 101 1000 300 2
ORCL 101 1000 500 3
ORCL 102 200 200 3
ORCL 103 600 100 3
ORCL 103 600 500 4
MSFT 111 500 200 11
MSFT 111 500 300 12
MSFT 112 500 100 12
MSFT 112 500 100 13
MSFT 112 500 300 14

The sell trade 101 with qty of 1000 has full maps to buy trades: 1 (200), 2(300) and a partial map of trade 3: 3(500),
The sell trade 102 with qty of 200 has to map the rest of trade 3 (200), a partial map too.
The sell trade 103 with qty of 600 has to map the rest of trade 3 (100) and full map of the buy trade 4

is it possible to do this by one sql?

Thanks a lot!
Steve

and Connor said...

I'm sure it is - its basically a version of the "bin fitting" problem, but I'd perhaps contend that maybe you *dont* want to be doing it in SQL. A potential solution will definitely require some analytics, and at first guess, I'd suspect you would also need the MODEL clause.

Which will get you a solution that probably isn't a maintainable as a simple PLSQL version. Here's my PLSQL version which simply walks down the buy and sells in order - and you call query in in SQL.


SQL>
SQL> drop type results_list;

Type dropped.

SQL>
SQL> create or replace type results as object
  2    (
  3  ticker varchar(20),
  4  sell_qty int,
  5  sell_price float,
  6  sell_transId int,
  7  buy_qty int,
  8  buy_price float,
  9  buy_transId int
 10  );
 11  /

Type created.

SQL>
SQL> create or replace
  2  type results_list as table of results
  3  /

Type created.

SQL>
SQL>
SQL> create or replace
  2  function stock_trader return results_list pipelined is
  3    l_alloc int := 0;
  4
  5    cursor c_sell is select sell.*
  6                     from sell
  7                     order by sell.ticker, sell.transid;
  8    cursor c_buy  is select buy.*
  9                     from buy
 10                     where buy.ticker in ( select ticker from sell )
 11                     order by buy.ticker, buy.transid;
 12
 13    type t_sell is table of c_sell%rowtype index by pls_integer;
 14    type t_buy is table of c_buy%rowtype index by pls_integer;
 15
 16    l_sell t_sell;
 17    l_buy  t_buy;
 18
 19    l_buy_idx int := 1;
 20    l_prev_s_ticker varchar2(10) := '*';
 21
 22  begin
 23    open c_sell; fetch c_sell bulk collect into l_sell; close c_sell;
 24    open c_buy;  fetch c_buy  bulk collect into l_buy;  close c_buy;
 25
 26    for i in 1 .. l_sell.count loop
 27       if l_sell(i).ticker != l_prev_s_ticker then
 28          l_alloc := 0;
 29          l_prev_s_ticker := l_sell(i).ticker;
 30       end if;
 31       l_alloc := l_alloc + l_sell(i).qty;
 32
 33       if l_alloc > 0 then
 34         loop
 35           if l_buy(l_buy_idx).ticker = l_sell(i).ticker then
 36              if l_buy(l_buy_idx).qty > l_alloc then
 37                l_buy(l_buy_idx).qty := l_buy(l_buy_idx).qty - l_alloc;
 38                pipe row ( results(l_sell(i).ticker, l_sell(i).qty, l_sell(i).price, l_sell(i).transid, l_alloc , l_buy(l_buy_idx).price,
 l_buy(l_buy_idx).transid) );
 39                l_alloc := 0;
 40                exit;
 41              else
 42                l_alloc := l_alloc - l_buy(l_buy_idx).qty;
 43                pipe row ( results(l_sell(i).ticker, l_sell(i).qty, l_sell(i).price, l_sell(i).transid, l_buy(l_buy_idx).qty , l_buy(l_bu
y_idx).price, l_buy(l_buy_idx).transid) );
 44                l_buy(l_buy_idx).qty := 0;
 45                l_buy_idx := l_buy_idx + 1;
 46              end if;
 47           else
 48             exit;
 49           end if;
 50         end loop;
 51       end if;
 52
 53    end loop;
 54    return;
 55  end;
 56  /

Function created.

SQL> sho err
No errors.
SQL>
SQL> select * from table(stock_trader);

TICKER                 SELL_QTY SELL_PRICE SELL_TRANSID    BUY_QTY  BUY_PRICE BUY_TRANSID
-------------------- ---------- ---------- ------------ ---------- ---------- -----------
MSFT                        500       44.5          111        200       44.5          11
MSFT                        500       44.5          111        300       45.5          12
MSFT                        500       45.5          112        100       45.5          12
MSFT                        500       45.5          112        100       44.5          13
MSFT                        500       45.5          112        300       45.5          14
ORCL                       1000       34.5          101        200       32.5           1
ORCL                       1000       34.5          101        300       33.5           2
ORCL                       1000       34.5          101        500       34.5           3
ORCL                        200         35          102        200       34.5           3
ORCL                        600       35.5          103        100       34.5           3
ORCL                        600       35.5          103        500       35.5           4

11 rows selected.

SQL>


If you want to pursue the SQL variant, take a look here

http://aprogrammerwrites.eu/?p=803#.VneAFUrRJBc

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Bin fitting?

Stew Ashton, December 21, 2015 - 9:46 am UTC

This question reminds me of my old COBOL batch programming days, when I often had to sort two input datasets then walk through them in tandem.

I definitely go along with the PL/SQL answer, which is efficient because each table is accessed just once.

On the other hand, I don't see this as a "bin fitting" problem. With "bin fitting", you either have a fixed number of "bins" or a fixed bin size. Here you have neither: it's more a matter of finding the intersections between the two tables based on the FIFO quantities.

Here is a SQL solution using analytics: it calculates ranges based on the quantities ordered by transaction ID, then finds where the ranges intersect. This should confirm that the PL/SQL solution is not only more efficient but easier to maintain.
with s_ranges as (
  select s.*,
  sum(qty) over(partition by ticker order by transid) - qty from_qty,
  sum(qty) over(partition by ticker order by transid) to_qty
  from sell s
)
, b_ranges as (
  select b.*,
  sum(qty) over(partition by ticker order by transid) - qty from_qty,
  sum(qty) over(partition by ticker order by transid) to_qty
  from buy b
),
base_froms as (
  select ticker, from_qty, 
  lead(from_qty) over(partition by ticker order by from_qty) - from_qty qty
  from (
    select distinct ticker, from_qty from (
      select ticker, from_qty, to_qty from s_ranges
      union
      select ticker, from_qty, to_qty from b_ranges
    )
    unpivot(from_qty for col in (from_qty, to_qty))
  )
)
select bf.ticker,
  s.qty sell_qty, s.price sell_price, s.transid sell_transid,
  bf.qty match_qty,
  b.qty buy_qty,  b.price buy_price,  b.transid buy_transid
from base_froms bf
join s_ranges s on bf.ticker = s.ticker
  and s.from_qty <= bf.from_qty and bf.from_qty < s.to_qty
join b_ranges b on bf.ticker = b.ticker
  and b.from_qty <= bf.from_qty and bf.from_qty < b.to_qty
order by bf.ticker, bf.from_qty;

TICKER   SELL_QTY SELL_PRICE SELL_TRANSID  MATCH_QTY    BUY_QTY  BUY_PRICE BUY_TRANSID
------ ---------- ---------- ------------ ---------- ---------- ---------- -----------
MSFT          500       44.5          111        200        200       44.5          11
MSFT          500       44.5          111        300        400       45.5          12
MSFT          500       45.5          112        100        400       45.5          12
MSFT          500       45.5          112        100        100       44.5          13
MSFT          500       45.5          112        300        300       45.5          14
ORCL         1000       34.5          101        200        200       32.5           1
ORCL         1000       34.5          101        300        300       33.5           2
ORCL         1000       34.5          101        500        800       34.5           3
ORCL          200         35          102        200        800       34.5           3
ORCL          600       35.5          103        100        800       34.5           3
ORCL          600       35.5          103        500        700       35.5           4

Connor McDonald
December 21, 2015 - 12:02 pm UTC

Thanks for contribution Stew. Have a good xmas.

Oops!

Stew Ashton, December 21, 2015 - 1:33 pm UTC

Thanks for the good wishes for Christmas. I wish the team and every reader a Merry Christmas (or equivalent holiday) and a Happy New Year.

I just noticed that there seems to be a "bug" in the requirement and in the solutions, including mine:

For ORCL, 1800 total shares are sold and 2000 are bought. The desired output seems to imply that the same number of shares are bought and sold. Shouldn't there be an extra line with the 200 extra bought shares?
Connor McDonald
December 21, 2015 - 11:54 pm UTC

I'll leave this for the original poster to comment on whether this is needed
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library