2

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.

Improve this question
17
  • Your use of the word "index" is confusing. I know that you intend it with respect to a stock index. But SQL has indexes to structure data, which is the primary word usage people may read. I think what you want is to create the SELECT statement, correct? Commented Aug 26, 2021 at 21:18
  • Thanks for pointing that out! I clarified it. I think I want the data in another table so more calculations and moving averages can be fitted into it. Commented Aug 27, 2021 at 7:43
  • please can you explain how you are calculating the daily changes in your exg stock tables.? eg Stock A 2021-08-26 95 97 2,03%............i would have thought it would have been ((Closeprice-Openprice)/Openprice*100) ....= 2.11 thoughts>? Commented Aug 27, 2021 at 14:21
  • Is the list of tables to join static, or do you want the list of tables to be dynamic? Commented Aug 27, 2021 at 15:04
  • Graham, sorry about the sample data. I just put in random numbers. Your calculation is correct. Robert, not entirely sure I understand the question. Can you elaborate? Commented Aug 27, 2021 at 20:40

3 Answers 3

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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
Improve this answer

3 Comments

You are correct with the need for ad-hoc analysis. I am not sure about the database design, if one table per stock is the way to go or as you say, everything in to one. If I later want moving averages, what's best? One table per stock (just add a computed column) or all stocks in one table and you solve it with the select statement for one stock and calculate the moving average in that select statement? I have the stock data now in excel but I can fetch it with an API as well, I am doing this to learn SQL as well so I dont want to go back to updating excel spreadsheets again...
I don't know what is "best" for you...suggest you explore various options and your definitions of "index". Maybe when you have a clearer picture of what you wish to do and can provide real numbers of stocks you follow, how often you add new stocks, the number of rows per stock, and the ad hoc metrics you wish to deploy...perhaps it would be sensible to post a new question
So, do you or perhaps anyone else know the answer to my original question?
0

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
Improve this answer

1 Comment

I don't understand your calculation in "your_index". Perhaps you have misunderstood the purpose of my "index"? I think you'd have trust me on this one regarding the use of my "index". If you have a select statement that calculates it then I would be very grateful to see it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.