Home » SQL & PL/SQL » SQL & PL/SQL » difficult task (10g)
| difficult task [message #687560] |
Wed, 05 April 2023 05:32  |
 |
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
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
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
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)
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
(1) 10 shares of 5 rubles
(2) 3 shares of 6 rubles
Realized financial result: 5 * ($7 - $4) + 12 * ($7 - $6) = $27
After that
(5) Sold 05.03.23 20 shares at $6 per share
(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
Final financial result: $17
-2 shares at $6 left in the position
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$ |
|
|
|
| Re: difficult task [message #687561 is a reply to message #687560] |
Wed, 05 April 2023 06:01  |
John Watson
Messages: 8880
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 |
|
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;
|
|
|
|
| Re: difficult task [message #687564 is a reply to message #687563] |
Wed, 05 April 2023 12:25 |
 |
Barbara Boehmer
Messages: 9063
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
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.
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.
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.
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
|
|
|
|
|
|
| Re: difficult task [message #687566 is a reply to message #687565] |
Wed, 05 April 2023 13:24 |
John Watson
Messages: 8880
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 |
|
Barbara Boehmer
Messages: 9063
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: Mon Oct 02 20:10:30 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
