CODEWITHSUNDEEP
sql-servert-sql
deep_blue
deep_blue

Reputation: 43

Create new table based on values of other

I have several tables in SQL Server 2019 with this kind of stock price data

Stock A

CREATE TABLE dbo.StockA_DailyPrice(
   Date        DATE  NOT NULL PRIMARY KEY
  ,Openprice   DECIMAL(16,2)  NOT NULL
  ,Highprice   DECIMAL(16,2)  NOT NULL
  ,Lowprice    DECIMAL(16,2)  NOT NULL
  ,Closeprice  DECIMAL(16,2)  NOT NULL
  ,DailyChange DECIMAL(16,4)  NOT NULL
);
INSERT INTO StockA_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-25','155.5','159.35','155.5','158.7','0.0205');
INSERT INTO StockA_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-24','154.5','156.95','154.3','155.5','0.0064');
INSERT INTO StockA_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-23','150','154.55','150','154.45','0.0296');
INSERT INTO StockA_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-20','147.4','149.4','145.6','149.15','0.01187');
INSERT INTO StockA_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-19','147.8','149.05','144','147.4','-0.0027');

Stock B

CREATE TABLE StockB_DailyPrice(
   Date        Date NOT NULL PRIMARY KEY
  ,Openprice   DECIMAL(16,2) NOT NULL
  ,Highprice   DECIMAL(16,2)  NOT NULL
  ,Lowprice    DECIMAL(16,2) NOT NULL
  ,Closeprice  DECIMAL(16,2) NOT NULL
  ,DailyChange DECIMAL(16,4) NOT NULL
);
INSERT INTO StockB_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-24','79.1','80.6','79','80.1','0.0126');
INSERT INTO StockB_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-23','79','80.1','77.9','79.1','0.0012');
INSERT INTO StockB_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-20','77.8','79.7','77.7','78.9','0.0141');
INSERT INTO StockB_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-19','79.1','79.3','77.3','78.5','-0.0075');
INSERT INTO StockB_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-18','77.4','79.7','77.3','79.6','0.0284');
INSERT INTO StockB_DailyPrice(Date,Openprice,Highprice,Lowprice,Closeprice,DailyChange) VALUES ('2021-08-17','77.4','78.6','77.2','77.3','-0.0012');

I want to create an index (not SQL-index but a stock index, a calculation of multiple values) from 2 or more of these tables with historical prices for a stock. Perhaps with the data inserted into another table?

The starting dates of the historic stock prices vary so I'd like to choose which start date to start calculating the merger from. And the start date should have a starting index number, like 100.

The result of the merge between A and B (but It would be nice if the merger code is scalable to merge multiple stocks) should be.

StockA_StockB_Merge

Date Index
2021-08-23 (IndexTheDayBefore)*(1+((StockA.DailyChange+StockB.DailyChange)/NumberOfMergedStocks))
2021-08-20 (IndexTheDayBefore)*(1+((StockA.DailyChange+StockB.DailyChange)/NumberOfMergedStocks))
2021-08-19 100*(1+((StockA.DailyChange+StockB.DailyChange)/NumberOfMergedStocks))

Which would output these numbers

Date Index
2021-08-23 100.78*(1+((0.0296+0.0012)/2)) =102.34
2021-08-20 99.49*(1+((0.0141+0.0119)/2)) =100.78
2021-08-19 99.49

The calculation for 2021-08-19 is 100*(1+((-0.0075+-0.0027)/2)) which I couldn't for the life of me get into the table. The editor said it was wrongly formated code.

The basic idea is that if one stock goes up 3% and the other 1%, the average for that pair is 2% (for example, stocks in the same industry).

The exact calculation of the daily change can of course vary. The tricky part is choosing the start date and start index value and the calculation to go from there.

Sorry if this is a tricky question :) I'm new to T-SQL.

Upvotes: 2

Views: 184

Answers (3)

deep_blue
deep_blue

Reputation: 43

I started another thread since this was being a little diluted with other discussions.

And I changed my workflow a bit so that the starting price was added as the "first" row.

Update all the following values in column depending on previous value

Upvotes: 1

GrahamH
GrahamH

Reputation: 360

Sorry to be a pain ...but I just cannot get my head around your "index"....

Date StockId Openprice Closeprice DailyChange your_Index my index
23/08/2021 A 1005.00 1000.00 -0.0050
23/08/2021 B 5.00 1.00 -0.8000 159.62 100.00
20/08/2021 A 1001.00 1005.00 0.0040
20/08/2021 B 3.00 5.00 0.6667 267.13 100.90
19/08/2021 A 1000.00 1001.00 0.0010
19/08/2021 B 1.00 3.00 2.0000 200.05 100.30

Upvotes: 0

GrahamH
GrahamH

Reputation: 360

you need to get your various single stock tables into one table. But I get the impression you want to do a lot of ad-hoc analysis with lots of different stocks...so Excel may be easier, particularly as you say you are new to SQL...? Where are you sourcing your base info from and in what file type?

CREATE TABLE AllStocks(
    StockId varchar(3) NOT NULL,
    Date date NOT NULL,
    Openprice decimal(16, 2) NOT NULL,
    Highprice decimal(16, 2) NOT NULL,
    Lowprice decimal(16, 2) NOT NULL,
    Closeprice decimal(16, 2) NOT NULL,
    DailyChange decimal(16, 4) NOT NULL
)


INSERT INTO AllStocks
                (StockId, Date, Openprice, Highprice, Lowprice, Closeprice, DailyChange)
SELECT   'A' , Date, Openprice, Highprice, Lowprice, Closeprice, DailyChange
FROM      StockA_DailyPrice

INSERT INTO AllStocks
                (StockId, Date, Openprice, Highprice, Lowprice, Closeprice, DailyChange)
SELECT   'B' , Date, Openprice, Highprice, Lowprice, Closeprice, DailyChange
FROM      StockB_DailyPrice

SELECT * FROM Allstocks
ORDER BY Date DESC, StockId ASC

Upvotes: 0

Related Questions