Home » SQL & PL/SQL » SQL & PL/SQL » FIFO Algorithm to calculate Avg weighted value (Oracle 10g)
FIFO Algorithm to calculate Avg weighted value [message #652115] |
Thu, 02 June 2016 02:18 |
anil029
Messages: 15 Registered: February 2010
|
Junior Member |
|
|
Hi All,
I have list of records which is basically the Trade positions taken for a particular Fund (Please refer to the sheet attached - Trade_book_PRICE_Calc.xls). I have to calculate a PRICE column for each of the positions. For OPEN positions (TRANSACTION_NOTES = OPL)the PRICE value would be same as corresponding ORIG_TRADE_PRICE. But for closed position (TRANSACTION_NOTES = CLL), the value of PRICE column would be calculated from ORIG_TRADE_PRICE of corresponding OPEN positions with FIFO - Average weighted price calculation method.
Please refer to the sheet attached - Trade_book_PRICE_Calc.xls. I have explained the calculation method in Explanation column.
Can someone help me write a SQL script which yields me the PRICE column based on the existing columns (First 5 columns) after applying all the calculation logic explained.
Many Thanks
Anil
|
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652124 is a reply to message #652115] |
Thu, 02 June 2016 04:45 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
As you have some records in several lines you cannot use external tables.
You can load the file in a permanent table using SQL*Loader (I let you verify that explanation is filled):
SQL> create table trade (
2 transaction_notes varchar2(3),
3 fund varchar2(3),
4 trade_date date,
5 orig_trade_price number,
6 num_positions integer,
7 price number,
8 explanation varchar2(1000)
9 )
10 /
Table created.
SQL> host type trade.ctl
LOAD DATA
INFILE 'C:\Trade_book_PRICE_Calc.csv'
CONTINUEIF NEXT PRESERVE(4) != ','
INTO TABLE trade
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
transaction_notes CHAR,
fund CHAR,
trade_date DATE "DD/MM/YYYY",
orig_trade_price DECIMAL EXTERNAL,
num_positions INTEGER EXTERNAL,
price DECIMAL EXTERNAL,
explanation CHAR(1000)
)
SQL> host sqlldr michel/michel control=c:\trade.ctl skip=1
SQL*Loader: Release 11.2.0.4.0 - Production on Jeu. Juin 2 11:32:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 9
Commit point reached - logical record count 10
SQL> select transaction_notes, fund, trade_date, orig_trade_price, num_positions, price
2 from trade
3 order by trade_date;
TRA FUN TRADE_DATE ORIG_TRADE_PRICE NUM_POSITIONS PRICE
--- --- ---------- ---------------- ------------- ----------
OPL ABC 20/12/2013 123.625 130 123.625
CLL ABC 07/01/2014 123.484375 130 123.625
OPL ABC 09/01/2014 123.1875 13 123.1875
OPL ABC 14/01/2014 124.375 18 124.375
CLL ABC 16/01/2014 123.984375 9 123.1875
CLL ABC 28/01/2014 124.71875 14 124.03571
OPL ABC 31/01/2014 125.765625 4 125.765625
OPL ABC 18/02/2014 125.65625 14 125.65625
CLL ABC 19/02/2014 126.03125 3 124.375
CLL ABC 25/02/2014 125.46875 23 125.396739
10 rows selected.
Quote:the value of PRICE column would be calculated from ORIG_TRADE_PRICE of corresponding OPEN positions with FIFO - Average weighted price calculation method.
I don't know what is this.
Please post the result you want for the data you gave as I did it above.
Before, Please read and understand How to use [code] tags and make your code easier to read.
[Updated on: Thu, 02 June 2016 07:43] Report message to a moderator
|
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652157 is a reply to message #652115] |
Thu, 02 June 2016 20:45 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
My understanding of the problem, which may or may not be correct, is that you have an Oracle table with five columns: transaction_notes, fund, trade_date, orig_trade_price, and num_positions, and you want a query to select those columns, and also produce a price column, calculated on a FIFO basis. The data sheet that you attached, containing the desired price column values, and an explanation column, explaining the derivation of the price column values is purely for explaining it to us and you do not need a SQL*Loader script or external table for loading that data into an Oracle table. Is this correct? If so, please see the demonstration below, in which I have provided a function that calculates the price, as per your explanations, and used that function in a query. This is the first thing that came to mind. There may be a way to simplify the function or use a query alone without a function.
-- data you provided:
SCOTT@orcl_12.1.0.2.0> SELECT transaction_notes, fund, trade_date, orig_trade_price, num_positions
2 FROM trade
3 ORDER BY trade_date
4 /
TRA FUN TRADE_DATE ORIG_TRADE_PRICE NUM_POSITIONS
--- --- --------------- ---------------- -------------
OPL ABC Fri 20-Dec-2013 123.625 130
CLL ABC Tue 07-Jan-2014 123.484375 130
OPL ABC Thu 09-Jan-2014 123.1875 13
OPL ABC Tue 14-Jan-2014 124.375 18
CLL ABC Thu 16-Jan-2014 123.984375 9
CLL ABC Tue 28-Jan-2014 124.71875 14
OPL ABC Fri 31-Jan-2014 125.765625 4
OPL ABC Tue 18-Feb-2014 125.65625 14
CLL ABC Wed 19-Feb-2014 126.03125 3
CLL ABC Tue 25-Feb-2014 125.46875 23
10 rows selected.
-- function:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION get_price
2 (p_transaction_notes IN VARCHAR2,
3 p_fund IN VARCHAR2,
4 p_trade_date IN DATE,
5 p_orig_trade_price IN NUMBER,
6 p_num_positions IN NUMBER)
7 RETURN NUMBER
8 AS
9 v_price NUMBER;
10 BEGIN
11 IF p_transaction_notes = 'OPL' THEN v_price := p_orig_trade_price;
12 ELSE
13 SELECT SUM (orig_trade_price) / SUM (ocv) price
14 INTO v_price
15 FROM (SELECT o.orig_trade_price, o.cv ocv,
16 ROW_NUMBER () OVER (ORDER BY o.trade_date) rn2
17 FROM (SELECT trade_date, orig_trade_price, cv,
18 ROW_NUMBER () OVER (ORDER BY trade_date) rn
19 FROM (SELECT trade_date, orig_trade_price, COLUMN_VALUE cv
20 FROM (SELECT *
21 FROM trade
22 WHERE transaction_notes = 'OPL'
23 AND fund = p_fund
24 AND trade_date < p_trade_date),
25 TABLE
26 (CAST
27 (MULTISET
28 (SELECT 1
29 FROM DUAL
30 CONNECT BY LEVEL <= num_positions)
31 AS SYS.ODCINUMBERLIST)))) o,
32 (SELECT trade_date, orig_trade_price, cv,
33 ROW_NUMBER () OVER (ORDER BY trade_date) rn
34 FROM (SELECT trade_date, orig_trade_price, COLUMN_VALUE cv
35 FROM (SELECT *
36 FROM trade
37 WHERE transaction_notes = 'CLL'
38 AND fund = p_fund
39 AND trade_date < p_trade_date),
40 TABLE
41 (CAST
42 (MULTISET
43 (SELECT -1
44 FROM DUAL
45 CONNECT BY LEVEL <= num_positions)
46 AS SYS.ODCINUMBERLIST)))) c
47 WHERE o.rn = c.rn (+)
48 AND c.rn IS NULL)
49 WHERE rn2 <= p_num_positions;
50 END IF;
51 RETURN v_price;
52 END get_price;
53 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- query:
SCOTT@orcl_12.1.0.2.0> SELECT transaction_notes, fund, trade_date, orig_trade_price, num_positions,
2 get_price (transaction_notes, fund, trade_date, orig_trade_price, num_positions) price
3 FROM trade
4 ORDER BY trade_date
5 /
TRA FUN TRADE_DATE ORIG_TRADE_PRICE NUM_POSITIONS PRICE
--- --- --------------- ---------------- ------------- ----------
OPL ABC Fri 20-Dec-2013 123.625 130 123.625
CLL ABC Tue 07-Jan-2014 123.484375 130 123.625
OPL ABC Thu 09-Jan-2014 123.1875 13 123.1875
OPL ABC Tue 14-Jan-2014 124.375 18 124.375
CLL ABC Thu 16-Jan-2014 123.984375 9 123.1875
CLL ABC Tue 28-Jan-2014 124.71875 14 124.035714
OPL ABC Fri 31-Jan-2014 125.765625 4 125.765625
OPL ABC Tue 18-Feb-2014 125.65625 14 125.65625
CLL ABC Wed 19-Feb-2014 126.03125 3 124.375
CLL ABC Tue 25-Feb-2014 125.46875 23 125.396739
10 rows selected.
|
|
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652176 is a reply to message #652175] |
Fri, 03 June 2016 11:54 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quite strange, when I open the downloaded CSV file with an hexadecimal editor, last record for instance, between "Hence PRICE value would" and "(5*124.375", I have 0x0D-0x0A (\r\n) so a new line for Windows.
If i remove the CONTINUEIF, SQL*Loader reports 12 records instead of 10 and 4 of them are bad which is consistent with the fact I see 2 double-line records (the 6th and the last ones).
[Updated on: Fri, 03 June 2016 11:55] Report message to a moderator
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652177 is a reply to message #652176] |
Fri, 03 June 2016 12:30 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 03 June 2016 09:54
Quite strange, when I open the downloaded CSV file with an hexadecimal editor, last record for instance, between "Hence PRICE value would" and "(5*124.375", I have 0x0D-0x0A (\r\n) so a new line for Windows.
If i remove the CONTINUEIF, SQL*Loader reports 12 records instead of 10 and 4 of them are bad which is consistent with the fact I see 2 double-line records (the 6th and the last ones).
I am baffled. I don't know if posting a copy and paste of what I did will shed any light on the problem or not, but here it is.
SCOTT@orcl_12.1.0.2.0> host type trade.ctl
options(skip=1)
LOAD DATA
INFILE Trade_book_PRICE_Calc.csv
INTO TABLE trade
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
transaction_notes CHAR,
fund CHAR,
trade_date DATE "DD/MM/YYYY",
orig_trade_price DECIMAL EXTERNAL,
num_positions INTEGER EXTERNAL,
price DECIMAL EXTERNAL,
explanation CHAR(1000)
)
SCOTT@orcl_12.1.0.2.0> create table trade (
2 transaction_notes varchar2(3),
3 fund varchar2(3),
4 trade_date date,
5 orig_trade_price number,
6 num_positions integer,
7 price number,
8 explanation varchar2(1000)
9 )
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=trade.ctl log=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Fri Jun 3 10:26:19 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 10
Table TRADE:
10 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> column explanation format a30 word_wrapped
SCOTT@orcl_12.1.0.2.0> select * from trade
2 /
TRA FUN TRADE_DATE ORIG_TRADE_PRICE NUM_POSITIONS PRICE EXPLANATION
--- --- --------------- ---------------- ------------- ---------- ------------------------------
OPL ABC Fri 20-Dec-2013 123.625 130 123.625 Same as of ORIG_TRADE_PRICE
CLL ABC Tue 07-Jan-2014 123.484375 130 123.625 As No. of positions closed is
same as no of positions open,
the PRICE value would be
ORIG_TRADE_PRICE of
corresponding OPEN positions
OPL ABC Thu 09-Jan-2014 123.1875 13 123.1875 Same as of ORIG_TRADE_PRICE
OPL ABC Tue 14-Jan-2014 124.375 18 124.375 Same as of ORIG_TRADE_PRICE
CLL ABC Thu 16-Jan-2014 123.984375 9 123.1875 No. of positions closed is 9.
Hence the PRICE value would be
ORIG_TRADE_PRICE of First In
Open Positions (3rd row). As
13>9 , the PRICE would be
corresponding ORIG_TRADE_PRICE
for all 9 closed positions.
CLL ABC Tue 28-Jan-2014 124.71875 14 124.03571 No. of positons closed is 14.
First 4 positions would come
from 3rd row (left afer
previous closed position) and
remaining 10 would come
from 4th row. Hence PRICE
value would be (4*123.1875 +
10*124.375)/14 = 124.03571
OPL ABC Fri 31-Jan-2014 125.765625 4 125.765625 Same as of ORIG_TRADE_PRICE
OPL ABC Tue 18-Feb-2014 125.65625 14 125.65625 Same as of ORIG_TRADE_PRICE
CLL ABC Wed 19-Feb-2014 126.03125 3 124.375 These 3 positions would be
closed from the 8 positions
still remaining in 4th row.
Hence PRICE value would be
ORIG_TRADE_PRICE of 4th row.
CLL ABC Tue 25-Feb-2014 125.46875 23 125.396739 5 positons from 4th
row(remained after previous
closed positions),4 from 7th
row and 14 from 8th row. Hence
PRICE value would
(5*124.375 + 4*125.765625 +
14*125.65625)/23 = 125.396739
10 rows selected.
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652178 is a reply to message #652177] |
Fri, 03 June 2016 13:29 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The display of your last row seems to also show a new line at the same place but maybe it is an illusion due to word_wrapped option.
Can you post dump(EXPLANATION,16) for this row.
The part "would%(5*124" is for me "77,6f,75,6c,64,28,35,2a,31,32,34" after loading with CONTINUEOF and "77 6F 75 6C 64 0D 0A 28 35 2A 31 32 34" in file.
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652179 is a reply to message #652178] |
Fri, 03 June 2016 14:43 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I don't see any 0D 0A.
SCOTT@orcl_12.1.0.2.0> SELECT DUMP (explanation, 16) FROM trade
2 /
DUMP(EXPLANATION,16)
----------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=134: 41,73,20,4e,6f,2e,20,6f,66,20,70,6f,73,69,74,69,6f,6e,73,20,63,6c,6f,73,65,64,20,69,73,20,73,61,6d,65,20,61,73,20,6
e,6f,20,6f,66,20,70,6f,73,69,74,69,6f,6e,73,20,6f,70,65,6e,2c,20,74,68,65,20,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64
,20,62,65,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45,20,6f,66,20,63,6f,72,72,65,73,70,6f,6e,64,69,6e,67,20,4f,50,45,4e,20,
70,6f,73,69,74,69,6f,6e,73
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=203: 4e,6f,2e,20,6f,66,20,70,6f,73,69,74,69,6f,6e,73,20,63,6c,6f,73,65,64,20,69,73,20,39,2e,20,48,65,6e,63,65,20,74,68,6
5,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64,20,62,65,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45,20,6f,66,20,46
,69,72,73,74,20,49,6e,20,4f,70,65,6e,20,50,6f,73,69,74,69,6f,6e,73,20,28,33,72,64,20,72,6f,77,29,2e,20,41,73,20,31,33,3e,39,20,2c,
20,74,68,65,20,50,52,49,43,45,20,77,6f,75,6c,64,20,62,65,20,63,6f,72,72,65,73,70,6f,6e,64,69,6e,67,20,4f,52,49,47,5f,54,52,41,44,4
5,5f,50,52,49,43,45,20,66,6f,72,20,61,6c,6c,20,39,20,63,6c,6f,73,65,64,20,70,6f,73,69,74,69,6f,6e,73,2e
Typ=1 Len=219: 4e,6f,2e,20,6f,66,20,70,6f,73,69,74,6f,6e,73,20,63,6c,6f,73,65,64,20,69,73,20,31,34,2e,20,46,69,72,73,74,20,34,20,7
0,6f,73,69,74,69,6f,6e,73,20,77,6f,75,6c,64,20,63,6f,6d,65,20,66,72,6f,6d,20,33,72,64,20,72,6f,77,20,28,6c,65,66,74,20,61,66,65,72
,20,70,72,65,76,69,6f,75,73,20,63,6c,6f,73,65,64,20,70,6f,73,69,74,69,6f,6e,29,20,61,6e,64,20,72,65,6d,61,69,6e,69,6e,67,20,31,30,
20,77,6f,75,6c,64,20,63,6f,6d,65,a,20,66,72,6f,6d,20,34,74,68,20,72,6f,77,2e,20,48,65,6e,63,65,20,50,52,49,43,45,20,76,61,6c,75,65
,20,77,6f,75,6c,64,20,62,65,20,28,34,2a,31,32,33,2e,31,38,37,35,20,2b,20,31,30,2a,31,32,34,2e,33,37,35,29,2f,31,34,20,3d,20,31,32,
34,2e,30,33,35,37,31
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=27: 53,61,6d,65,20,61,73,20,6f,66,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,45
Typ=1 Len=138: 54,68,65,73,65,20,33,20,70,6f,73,69,74,69,6f,6e,73,20,77,6f,75,6c,64,20,62,65,20,63,6c,6f,73,65,64,20,66,72,6f,6d,2
0,74,68,65,20,38,20,70,6f,73,69,74,69,6f,6e,73,20,73,74,69,6c,6c,20,72,65,6d,61,69,6e,69,6e,67,20,69,6e,20,34,74,68,20,72,6f,77,2e
,20,48,65,6e,63,65,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64,20,62,65,20,4f,52,49,47,5f,54,52,41,44,45,5f,50,52,49,43,
45,20,6f,66,20,34,74,68,20,72,6f,77,2e
Typ=1 Len=183: 35,20,70,6f,73,69,74,6f,6e,73,20,66,72,6f,6d,20,34,74,68,20,72,6f,77,28,72,65,6d,61,69,6e,65,64,20,61,66,74,65,72,2
0,70,72,65,76,69,6f,75,73,20,63,6c,6f,73,65,64,20,70,6f,73,69,74,69,6f,6e,73,29,2c,34,20,66,72,6f,6d,20,37,74,68,20,72,6f,77,20,61
,6e,64,20,31,34,20,66,72,6f,6d,20,38,74,68,20,72,6f,77,2e,20,48,65,6e,63,65,20,50,52,49,43,45,20,76,61,6c,75,65,20,77,6f,75,6c,64,
a,28,35,2a,31,32,34,2e,33,37,35,20,2b,20,34,2a,31,32,35,2e,37,36,35,36,32,35,20,2b,20,31,34,2a,31,32,35,2e,36,35,36,32,35,29,2f,32
,33,20,3d,20,31,32,35,2e,33,39,36,37,33,39
10 rows selected.
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652182 is a reply to message #652179] |
Fri, 03 June 2016 15:03 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
77,6f,75,6c,64,a,28,35,2a,31,32,34
You indeed have a \n in your data but just \n with no \r.
I directly opened the file with the hexadecimal editor instead of downloading and then open it with another editor and there is indeed only \n. This was my editor which has automatically converted \n to \r\n.
So this is the explanation of the mystery.
|
|
|
Re: FIFO Algorithm to calculate Avg weighted value [message #652183 is a reply to message #652179] |
Fri, 03 June 2016 15:05 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In place of your 0D 0A I see a which generates a linefeed in the output of the data after the word would, but apparently SQL*Loader did not see it as a line feed.
SCOTT@orcl_12.1.0.2.0> SELECT SUBSTR (explanation, INSTR (explanation, 'would'), 12) original,
2 DUMP (SUBSTR (explanation, INSTR (explanation, 'would'), 12), 16) the_dump
3 FROM trade
4 WHERE explanation LIKE '%5*124%'
5 /
ORIGINAL
------------------------------------------------
THE_DUMP
--------------------------------------------------------------------------------
would
(5*124
Typ=1 Len=12: 77,6f,75,6c,64,a,28,35,2a,31,32,34
1 row selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 29 00:35:04 CDT 2024
|