Home » SQL & PL/SQL » SQL & PL/SQL » difficult task (10g)
difficult task [message #687560] |
Wed, 05 April 2023 05:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/693b67de3e8b183090feca9f1f28b208?s=64&d=mm&r=g) |
urmas19
Messages: 7 Registered: March 2023
|
Junior Member |
|
|
Hello!
Can anyone help?
There is a difficult task on the stock market.
There are the following tables:
shares - List of shares (table contains several hundred entries)
ID INT - Share ID (Primary key)
CODE VARCHAR2(30) - Stock code
NAME VARCHAR2(100) - Share name
quotes - Stock quotes (the table contains quotes for several years)
ID_SHARE INT - share ID
DT DATE - date of quotation (dates for one share may be with gaps, if there is no quotation on a given date, then it is considered that the quotation has not changed since the previous date)
VALUE NUMBER - the value of the market quote on the specified date
deals - Deals for buying and selling shares (the table contains several million records)
ID INT - Deal ID (Primary key)
ID_SHARE INT - Share ID
DT DATE - Date and time of the transaction
DTYPE CHAR(1) - Deal type ("B" - buy, "S" - sell)
N INT - Number of securities
VALUE NUMBER - Price per piece
positions - Stock position
ID_TICKER INT - Stock ID
N INT - Number of securities in position
P_AVERAGE NUMBER - Average purchase price of a position per share
P_RES_REALIZED NUMBER - Realized financial result
P_RES_UNREALIZED NUMBER - Unrealized financial result (calculated as the difference between the market price of the share and the purchase price of the share, multiplied by the number of shares in the position)
Problem:
1. Come up with names for the specified tables and columns in them, write scripts to create them (with all the necessary constraints and indexes), as well as those tables that may still be required to solve the problem.
2. Develop a procedure that receives one parameter as input - position date. The procedure should calculate the LIFO trade position for each stock on the specified date and place it in the Stock Position table.
3. Assumptions:
a. We assume that the set of deals and quotes do not change during the operation of the procedure
b. Stock Position table can be cleared each time before use
4. The position can be either long or short (they sold more securities than they bought before).
5. Deals made at the same time are additionally sorted by identifier.
(!!!) LIFO - When selling shares, those of the remaining ones that were bought most recently are sold first, for example:
Example:
=======
(1) Bought 01.MAR.23 10 shares at $5 per share
(2) Bought 02.MAR.23 15 shares at $6 per share
(3) Bought 03.MAR.23 5 shares at $4 per share
(4) Sold on 04.MAR.23 17 shares at $7 per share
As a result of the sale, the following deals will remain in the position as of the evening of 04/03/23:
(1) 10 shares of 5 rubles
(2) 3 shares of 6 rubles
Total: 13 shares at an average price of: (10 * $5 + 3 * $6) / 13 = $5.23
Realized financial result: 5 * ($7 - $4) + 12 * ($7 - $6) = $27
After that
(5) Sold 05.03.23 20 shares at $6 per share
As a result of the sale, the following deals will remain in the position:
(5) -7 shares at $6
Realized financial result from this sale: 13 * (6 – 68/13) = $10
Final financial result: $37
(6) Sold on 06.MAR.23 10 shares at $7 per share
(7) Bought 10.MAR.23 15 shares at $8 per share
Realized financial result from this purchase: 10 * (7$ - 8$) + 5 * (6$ - 8$) = -20$
Final financial result: $17
-2 shares at $6 left in the position
Let the market price for this share on 11.MAR.23 be $9 per share.
The general position as of 11.MAR.23 is as follows:
Number of securities in position: -2
Average purchase price: $6
Realized financial result: $17
Unrealized financial result: -6$
Thank you very much!
|
|
|
Re: difficult task [message #687561 is a reply to message #687560] |
Wed, 05 April 2023 06:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It appears that you may be in a country where using Oracle licences is no longer legal, this may impact on people's willingness to assist.
What I will say is that this looks very like a college homework assignment. So you need to show what you have done so far, and explain where you are stuck.
|
|
|
|
Re: difficult task [message #687563 is a reply to message #687562] |
Wed, 05 April 2023 08:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/693b67de3e8b183090feca9f1f28b208?s=64&d=mm&r=g) |
urmas19
Messages: 7 Registered: March 2023
|
Junior Member |
|
|
I wrote this:
DROP TABLE shares CASCADE CONSTRAINTS
/
CREATE TABLE shares
(
id INT NOT NULL,
code VARCHAR2(30) NOT NULL,
name VARCHAR2(100) NOT NULL
)
/
COMMENT ON TABLE shares IS 'Companies'
/
CREATE UNIQUE INDEX shares_ui ON shares(id)
/
ALTER TABLE shares ADD CONSTRAINT shares_pk PRIMARY KEY(id)
/
INSERT INTO shares VALUES(1, 'C1', 'COMPANY1');
INSERT INTO shares VALUES(2, 'C2', 'COMPANY2');
DROP TABLE quotes CASCADE CONSTRAINTS
/
CREATE TABLE quotes(
id_share INT NOT NULL,
t DATE NOT NULL,
v FLOAT NOT NULL
)
/
COMMENT ON TABLE quotes IS 'Quotes'
/
CREATE UNIQUE INDEX quotes_ui ON quotes(id_share, t)
/
ALTER TABLE quotes ADD CONSTRAINT quotes_pk PRIMARY KEY(id_share, t)
/
ALTER TABLE quotes ADD CONSTRAINT quotes_id_fk FOREIGN KEY(id_share) REFERENCES shares(id)
/
INSERT INTO quotes VALUES(1, to_date('11/03/2023', 'dd/mm/yyyy'), 9);
DROP TABLE deals CASCADE CONSTRAINTS
/
CREATE TABLE deals(
id INT NOT NULL,
id_share INT NOT NULL,
t DATE NOT NULL,
dtype CHAR(1) NOT NULL,
n INT NOT NULL,
value FLOAT NOT NULL
)
/
COMMENT ON TABLE deals IS 'Deals'
/
CREATE UNIQUE INDEX deals_ui ON deals(id)
/
CREATE UNIQUE INDEX deals_ui1 ON deals(id_share, t)
/
ALTER TABLE deals ADD CONSTRAINT deals_pk PRIMARY KEY(id)
/
ALTER TABLE deals ADD CONSTRAINT deals_id_share_fk FOREIGN KEY(id_share) REFERENCES shares(id)
/
INSERT INTO deals VALUES(1, 1, to_date('01/03/2023', 'dd/mm/yyyy'), 'B', 10, 5);
INSERT INTO deals VALUES(2, 1, to_date('02/03/2023', 'dd/mm/yyyy'), 'B', 15, 6);
INSERT INTO deals VALUES(3, 1, to_date('03/03/2023', 'dd/mm/yyyy'), 'B', 5, 4);
INSERT INTO deals VALUES(4, 1, to_date('04/03/2023', 'dd/mm/yyyy'), 'S', 17, 7);
INSERT INTO deals VALUES(5, 1, to_date('05/03/2023', 'dd/mm/yyyy'), 'S', 20, 6);
INSERT INTO deals VALUES(6, 1, to_date('06/03/2023', 'dd/mm/yyyy'), 'S', 10, 7);
INSERT INTO deals VALUES(7, 1, to_date('10/03/2023', 'dd/mm/yyyy'), 'B', 15, 8);
DROP TABLE positions CASCADE CONSTRAINTS
/
CREATE TABLE positions(
id_share INT NOT NULL,
n INT NOT NULL,
p_average NUMBER NOT NULL,
p_result_realized NUMBER NOT NULL,
p_result_unrealized NUMBER NOT NULL
)
/
CREATE OR REPLACE PROCEDURE calculate_positions(
vInDateTime IN DATE;
)
AS
BEGIN
DELETE FROM positions;
...?
END;
/
BEGIN
calculate_positions(TO_DATE('11/03/2023', 'dd/mm/yyyy'));
END;
But I've no ideas how to realize this algorithm...
|
|
|
Re: difficult task [message #687564 is a reply to message #687563] |
Wed, 05 April 2023 12:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your tables and such look good, although you may want to make changes later.
For your procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 BEGIN
6 DELETE FROM positions;
7 END;
8 /
Warning: Procedure created with compilation errors.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('11/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
calculate_positions(TO_DATE('11/03/2023', 'dd/mm/yyyy'));
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00905: object SCOTT.CALCULATE_POSITIONS is invalid
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
The method that I like to use is start with the minimum that it takes to create a procedure, using 2 hyphens to comment out everything else temporarily, then test, fix if necessar, re-testing and fixing as necessary until the minimum works. Then add one piece and re-test and fix and so on. I don't add another part until I get what I have tested and working properly. I use show errors to check for any errors, where they are, and what they are. Initially, because the BEGIN section needs to have something, I use a single line that says, "NULL;".
So, I would start with commenting out all but the minimum on your procedure and test it.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions
2 -- (vInDateTime IN DATE;)
3 AS
4 BEGIN
5 NULL;
6 -- DELETE FROM positions;
7 END;
8 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
The above works, so I would remove the first hyphens in front of the input parameter
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions
2 (vInDateTime IN DATE;)
3 AS
4 BEGIN
5 NULL;
6 -- DELETE FROM positions;
7 END calculate_positions;
8 /
Warning: Procedure created with compilation errors.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
Errors for PROCEDURE CALCULATE_POSITIONS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/23 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
:= . ) , @ % default character
The symbol ";" was ignored.
The above tells me that on line 2, position 23, there is a semicolon that does not belong there, so I remove it and re-test.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions
2 (vInDateTime IN DATE)
3 AS
4 BEGIN
5 NULL;
6 -- DELETE FROM positions;
7 END calculate_positions;
8 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('11/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
It works, so remove he hyphens from in front of the delete statement and add a select statement after execution to confirm that it works. You may also want to add comment lines to remind yourself and any others what it is doing.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions
2 (vInDateTime IN DATE)
3 AS
4 BEGIN
5 -- "b. Stock Position table can be cleared each time before use"
6 DELETE FROM positions;
7 END calculate_positions;
8 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('11/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM positions
2 /
no rows selected
I suggest that you continue in this manner, adding the next piece.
|
|
|
|
Re: difficult task [message #687566 is a reply to message #687565] |
Wed, 05 April 2023 13:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I've Googled "lifo trade position algorithm", there are some descriptions out there. Try a few other variations in the search. If you don't understand the algorithm, you can't create a procedure to implement it.
|
|
|
Re: difficult task [message #687567 is a reply to message #687565] |
Wed, 05 April 2023 16:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
urmas19 wrote on Wed, 05 April 2023 11:14Barbara, thank you, but the problem is that I do not know how to implement the optimal algorithm on PL/SQL in procedure calculate_positions, which is described in section "example" which'll fill table positions.
It looks like the data to be put in the positions table must come from the deals table. So, you will need to loop through the rows of the deals table that match the date parameter and process them, inserting into or updating or deleting from the positions table depending on whether they are buying or selling and so forth. In order to apply LIFO (last in first out) accounting, you will need to loop through some of it in reverse order. I have thrown together some partial code, that may have some errors, as an example. I created and used a temp_positions table to circumvent constraints on columns that the partial code does not populate. I have then executed the procedure for one date at a time, followed by a select, so that you can see what the progressive results are that match the example in your requirements. You will need to figure out how to include the calculations for the remaining columns.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE temp_positions(
2 id_share INT,
3 n INT,
4 value NUMBER,
5 p_average NUMBER,
6 p_result_realized NUMBER,
7 p_result_unrealized NUMBER
8 )
9 /
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions
2 (vInDateTime IN DATE)
3 AS
4 v_n NUMBER;
5 v_value NUMBER;
6 BEGIN
7 -- "b. Stock Position table can be cleared each time before use"
8 -- may not want to do that here
9 -- DELETE FROM positions;
10 --
11 -- loop through deals table values for parameter date ordered by date and id
12 FOR d IN
13 (SELECT id, id_share, t, dtype, n, value
14 FROM deals
15 WHERE TRUNC (t) = TRUNC (vInDateTime)
16 ORDER BY t, id)
17 LOOP
18 -- if bought, then insert into position table
19 IF d.dtype = 'B' THEN
20 v_n := d.n;
21 v_value := d.value;
22 FOR p IN
23 (SELECT *
24 FROM (SELECT ROWNUM rn, id_share, n, value, p_average, p_result_realized, p_result_unrealized
25 FROM temp_positions
26 ORDER BY id_share DESC)
27 WHERE rn = 1)
28 LOOP
29 IF p.n < 0 THEN
30 v_n := v_n + p.n;
31 v_value := p.value;
32 DELETE FROM temp_positions WHERE id_share = p.id_share;
33 END IF;
34 END LOOP;
35 INSERT INTO temp_positions (id_share, n, value)
36 VALUES (d.id, v_n, v_value);
37 -- if sold, then delete from positions table in reverse (LIFO - last in first out) order:
38 ELSIF d.dtype = 'S' THEN
39 v_n := d.n;
40 WHILE v_n != 0 LOOP
41 FOR p IN
42 (SELECT ROWNUM rn, id_share, n, value, p_average, p_result_realized, p_result_unrealized
43 FROM temp_positions
44 ORDER BY id_share DESC)
45 LOOP
46 IF v_n = p.n THEN
47 DELETE FROM temp_positions WHERE id_share = p.id_share;
48 v_n := 0;
49 ELSIF v_n < p.n THEN
50 UPDATE temp_positions SET n = n - v_n WHERE id_share = p.id_share;
51 v_n := 0;
52 ELSIF v_n > p.n THEN
53 IF p.n < 0 THEN
54 INSERT INTO temp_positions (id_share, n, value)
55 VALUES (d.id, p.n - v_n, p.value);
56 DELETE FROM temp_positions WHERE id_share = p.id_share;
57 v_n := 0;
58 ELSE
59 IF p.rn != 1 THEN
60 DELETE FROM temp_positions WHERE id_share = p.id_share;
61 v_n := v_n - p.n;
62 ELSIF p.rn = 1 THEN
63 INSERT INTO temp_positions (id_share, n, value)
64 VALUES (d.id, p.n - v_n, d.value);
65 DELETE FROM temp_positions WHERE id_share = p.id_share;
66 v_n := 0;
67 END IF;
68 END IF;
69 END IF;
70 END LOOP;
71 END LOOP;
72 END IF;
73 END LOOP;
74 END calculate_positions;
75 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('01/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
1 10 5
1 row selected.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('02/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
1 10 5
2 15 6
2 rows selected.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('03/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
1 10 5
2 15 6
3 5 4
3 rows selected.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('04/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
1 10 5
2 3 6
2 rows selected.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('05/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
5 -7 6
1 row selected.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('06/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
6 -17 6
1 row selected.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 calculate_positions(TO_DATE('10/03/2023', 'dd/mm/yyyy'));
3 END;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT id_share, n, value FROM temp_positions order by id_share
2 /
ID_SHARE N VALUE
---------- ---------- ----------
7 -2 6
1 row selected.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:59:18 CDT 2024
|