Home » SQL & PL/SQL » SQL & PL/SQL » difficult task (10g)
difficult task [message #687560] Wed, 05 April 2023 05:32 Go to next message
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 messageGo to next message
John Watson
Messages: 8922
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 #687562 is a reply to message #687561] Wed, 05 April 2023 07:43 Go to previous messageGo to next message
urmas19
Messages: 7
Registered: March 2023
Junior Member
I have old version (Personal Edition) on my laptop and this one is the college homework Smile
Re: difficult task [message #687563 is a reply to message #687562] Wed, 05 April 2023 08:03 Go to previous messageGo to next message
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... Sad
Re: difficult task [message #687564 is a reply to message #687563] Wed, 05 April 2023 12:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #687565 is a reply to message #687564] Wed, 05 April 2023 13:14 Go to previous messageGo to next message
urmas19
Messages: 7
Registered: March 2023
Junior Member
Barbara, 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.
Re: difficult task [message #687566 is a reply to message #687565] Wed, 05 April 2023 13:24 Go to previous messageGo to next message
John Watson
Messages: 8922
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
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
urmas19 wrote on Wed, 05 April 2023 11:14
Barbara, 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.
Previous Topic: Sequence alternate
Next Topic: Design issue
Goto Forum:
  


Current Time: Fri Mar 29 04:56:16 CDT 2024