By: Dallas Snider | Updated: 2016-12-13 | Comments | Related: > TSQL
Problem
I need to calculate multiple Pearson product moment correlation coefficients for data that are stored in one SQL Server table. How can I do this?
Solution
In this tip, our data will be the closing price for 10 stocks for 67 consecutive trading days which was acquired through nasdaq.com. The goal will be to see which stocks' prices trend together and which move in opposite directions. The Pearson product moment correlation coefficient will be calculated between all combinations of stocks using T-SQL. A CROSS JOIN query will be used to generate the combinations of stocks while a second query will be used to calculate the Pearson coefficient.
The Pearson product moment correlation coefficient, also known more simply as the Pearson coefficient, is a mathematical calculation to determine how well two sets of data linearly correlate. The Pearson coefficient can have a value from -1 to +1 inclusive. The closer the Pearson coefficient is to +1, the stronger the positive correlation. In other words, as the values of the first measurement increase, so do the values of the second measurement. The closer the Pearson coefficient is to -1, the stronger the negative correlation. In other words, as the values of the first measurement increase, the values of the second measurement decrease. A Pearson coefficient near 0, whether positive or negative, indicates there is little to no correlation between the two sets of data.
Writing a T-SQL query to calculate the Pearson product moment correlation coefficient is fairly straightforward. However, because the calculation involves floating point numbers and division, we must be careful that all of our numbers are floating point so we don't end up with a Pearson coefficient having only the integer values -1, 0 or +1. We will look at three examples in this tip. The first is a positive correlation, the second is a negative correlation and the third has no correlation. Examining each of these cases will also help us to test our code to make sure it is accurate. In all three examples, we will be calculating the Pearson product moment correlation coefficient between Measurement A and Measurement B.
Let's begin by creating and loading our example table. The T-SQL code below will create a table that will have columns for the primary key, stock name, closing date and closing price. We are using the stock name in this example for simplicity and ease of explanation, instead of a stock ID column defined as an integer foreign key. The insert statements will insert 670 rows into the table.
create table dbo.tblStockPrices
(
pkStockPriceID int identity(1,1) not null primary key,
StockName varchar(32),
ClosingDate date,
ClosingPrice money
)
go
insert into dbo.tblStockPrices values('Exxon Mobil', '10/28/2016', 86.92)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/27/2016', 86.92)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/26/2016', 87.09)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/25/2016', 86.72)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/24/2016', 86.91)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/21/2016', 86.62)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/20/2016', 87.21)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/19/2016', 87.17)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/18/2016', 86.77)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/17/2016', 86.54)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/14/2016', 86.54)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/13/2016', 86.56)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/12/2016', 87.13)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/11/2016', 87.74)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/10/2016', 88.44)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/7/2016', 86.74)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/6/2016', 87.04)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/5/2016', 87)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/4/2016', 86.25)
insert into dbo.tblStockPrices values('Exxon Mobil', '10/3/2016', 87.05)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/30/2016', 87.28)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/29/2016', 86.46)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/28/2016', 86.9)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/27/2016', 83.24)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/26/2016', 83.06)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/23/2016', 83.45)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/22/2016', 83.54)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/21/2016', 83.3)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/20/2016', 82.54)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/19/2016', 83.83)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/16/2016', 84.03)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/15/2016', 85.08)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/14/2016', 84.6)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/13/2016', 85.21)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/12/2016', 87.29)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/9/2016', 86.84)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/8/2016', 89.05)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/7/2016', 88.24)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/6/2016', 88.57)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/2/2016', 87.42)
insert into dbo.tblStockPrices values('Exxon Mobil', '9/1/2016', 86.84)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/31/2016', 87.14)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/30/2016', 87.52)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/29/2016', 87.84)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/26/2016', 87.27)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/25/2016', 87.46)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/24/2016', 88.02)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/23/2016', 87.72)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/22/2016', 87.99)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/19/2016', 87.8)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/18/2016', 88.91)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/17/2016', 88.11)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/16/2016', 87.92)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/15/2016', 87.81)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/12/2016', 87.85)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/11/2016', 86.72)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/10/2016', 86.41)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/9/2016', 88.7)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/8/2016', 88.59)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/5/2016', 87.56)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/4/2016', 87.48)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/3/2016', 87.49)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/2/2016', 87.04)
insert into dbo.tblStockPrices values('Exxon Mobil', '8/1/2016', 85.86)
insert into dbo.tblStockPrices values('Exxon Mobil', '7/29/2016', 88.95)
insert into dbo.tblStockPrices values('Exxon Mobil', '7/28/2016', 90.2)
insert into dbo.tblStockPrices values('Exxon Mobil', '7/27/2016', 90.91)
insert into dbo.tblStockPrices values('Target', '10/28/2016', 67.62)
insert into dbo.tblStockPrices values('Target', '10/27/2016', 67.62)
insert into dbo.tblStockPrices values('Target', '10/26/2016', 68.59)
insert into dbo.tblStockPrices values('Target', '10/25/2016', 67.95)
insert into dbo.tblStockPrices values('Target', '10/24/2016', 68.26)
insert into dbo.tblStockPrices values('Target', '10/21/2016', 68.23)
insert into dbo.tblStockPrices values('Target', '10/20/2016', 67.71)
insert into dbo.tblStockPrices values('Target', '10/19/2016', 67.53)
insert into dbo.tblStockPrices values('Target', '10/18/2016', 67.36)
insert into dbo.tblStockPrices values('Target', '10/17/2016', 67.24)
insert into dbo.tblStockPrices values('Target', '10/14/2016', 68.11)
insert into dbo.tblStockPrices values('Target', '10/13/2016', 67.89)
insert into dbo.tblStockPrices values('Target', '10/12/2016', 67.86)
insert into dbo.tblStockPrices values('Target', '10/11/2016', 68.39)
insert into dbo.tblStockPrices values('Target', '10/10/2016', 68.86)
insert into dbo.tblStockPrices values('Target', '10/7/2016', 69.04)
insert into dbo.tblStockPrices values('Target', '10/6/2016', 68.41)
insert into dbo.tblStockPrices values('Target', '10/5/2016', 67.74)
insert into dbo.tblStockPrices values('Target', '10/4/2016', 68.65)
insert into dbo.tblStockPrices values('Target', '10/3/2016', 68.68)
insert into dbo.tblStockPrices values('Target', '9/30/2016', 68.68)
insert into dbo.tblStockPrices values('Target', '9/29/2016', 67.72)
insert into dbo.tblStockPrices values('Target', '9/28/2016', 67.82)
insert into dbo.tblStockPrices values('Target', '9/27/2016', 68.29)
insert into dbo.tblStockPrices values('Target', '9/26/2016', 67.22)
insert into dbo.tblStockPrices values('Target', '9/23/2016', 68.77)
insert into dbo.tblStockPrices values('Target', '9/22/2016', 68.56)
insert into dbo.tblStockPrices values('Target', '9/21/2016', 69.47)
insert into dbo.tblStockPrices values('Target', '9/20/2016', 68.62)
insert into dbo.tblStockPrices values('Target', '9/19/2016', 68.89)
insert into dbo.tblStockPrices values('Target', '9/16/2016', 69.23)
insert into dbo.tblStockPrices values('Target', '9/15/2016', 69.21)
insert into dbo.tblStockPrices values('Target', '9/14/2016', 68.2)
insert into dbo.tblStockPrices values('Target', '9/13/2016', 68.94)
insert into dbo.tblStockPrices values('Target', '9/12/2016', 69.3)
insert into dbo.tblStockPrices values('Target', '9/9/2016', 69)
insert into dbo.tblStockPrices values('Target', '9/8/2016', 69.46)
insert into dbo.tblStockPrices values('Target', '9/7/2016', 70.3)
insert into dbo.tblStockPrices values('Target', '9/6/2016', 70.1)
insert into dbo.tblStockPrices values('Target', '9/2/2016', 70.81)
insert into dbo.tblStockPrices values('Target', '9/1/2016', 70.58)
insert into dbo.tblStockPrices values('Target', '8/31/2016', 70.19)
insert into dbo.tblStockPrices values('Target', '8/30/2016', 70.39)
insert into dbo.tblStockPrices values('Target', '8/29/2016', 71.05)
insert into dbo.tblStockPrices values('Target', '8/26/2016', 70.35)
insert into dbo.tblStockPrices values('Target', '8/25/2016', 70.81)
insert into dbo.tblStockPrices values('Target', '8/24/2016', 71.73)
insert into dbo.tblStockPrices values('Target', '8/23/2016', 71.05)
insert into dbo.tblStockPrices values('Target', '8/22/2016', 70.39)
insert into dbo.tblStockPrices values('Target', '8/19/2016', 70.12)
insert into dbo.tblStockPrices values('Target', '8/18/2016', 70.3)
insert into dbo.tblStockPrices values('Target', '8/17/2016', 70.63)
insert into dbo.tblStockPrices values('Target', '8/16/2016', 75.48)
insert into dbo.tblStockPrices values('Target', '8/15/2016', 75.53)
insert into dbo.tblStockPrices values('Target', '8/12/2016', 75.81)
insert into dbo.tblStockPrices values('Target', '8/11/2016', 74.01)
insert into dbo.tblStockPrices values('Target', '8/10/2016', 73.05)
insert into dbo.tblStockPrices values('Target', '8/9/2016', 72.61)
insert into dbo.tblStockPrices values('Target', '8/8/2016', 75)
insert into dbo.tblStockPrices values('Target', '8/5/2016', 74.94)
insert into dbo.tblStockPrices values('Target', '8/4/2016', 74.14)
insert into dbo.tblStockPrices values('Target', '8/3/2016', 74.1)
insert into dbo.tblStockPrices values('Target', '8/2/2016', 73.89)
insert into dbo.tblStockPrices values('Target', '8/1/2016', 75.51)
insert into dbo.tblStockPrices values('Target', '7/29/2016', 75.33)
insert into dbo.tblStockPrices values('Target', '7/28/2016', 74.79)
insert into dbo.tblStockPrices values('Target', '7/27/2016', 75.25)
insert into dbo.tblStockPrices values('Wal-Mart', '10/28/2016', 69.83)
insert into dbo.tblStockPrices values('Wal-Mart', '10/27/2016', 69.83)
insert into dbo.tblStockPrices values('Wal-Mart', '10/26/2016', 69.59)
insert into dbo.tblStockPrices values('Wal-Mart', '10/25/2016', 69.36)
insert into dbo.tblStockPrices values('Wal-Mart', '10/24/2016', 69.19)
insert into dbo.tblStockPrices values('Wal-Mart', '10/21/2016', 68.34)
insert into dbo.tblStockPrices values('Wal-Mart', '10/20/2016', 68.73)
insert into dbo.tblStockPrices values('Wal-Mart', '10/19/2016', 68.89)
insert into dbo.tblStockPrices values('Wal-Mart', '10/18/2016', 68.87)
insert into dbo.tblStockPrices values('Wal-Mart', '10/17/2016', 68.22)
insert into dbo.tblStockPrices values('Wal-Mart', '10/14/2016', 68.45)
insert into dbo.tblStockPrices values('Wal-Mart', '10/13/2016', 68.23)
insert into dbo.tblStockPrices values('Wal-Mart', '10/12/2016', 67.46)
insert into dbo.tblStockPrices values('Wal-Mart', '10/11/2016', 67.39)
insert into dbo.tblStockPrices values('Wal-Mart', '10/10/2016', 67.98)
insert into dbo.tblStockPrices values('Wal-Mart', '10/7/2016', 68.7)
insert into dbo.tblStockPrices values('Wal-Mart', '10/6/2016', 69.36)
insert into dbo.tblStockPrices values('Wal-Mart', '10/5/2016', 71.67)
insert into dbo.tblStockPrices values('Wal-Mart', '10/4/2016', 71.75)
insert into dbo.tblStockPrices values('Wal-Mart', '10/3/2016', 72.01)
insert into dbo.tblStockPrices values('Wal-Mart', '9/30/2016', 72.12)
insert into dbo.tblStockPrices values('Wal-Mart', '9/29/2016', 70.73)
insert into dbo.tblStockPrices values('Wal-Mart', '9/28/2016', 71.79)
insert into dbo.tblStockPrices values('Wal-Mart', '9/27/2016', 72.33)
insert into dbo.tblStockPrices values('Wal-Mart', '9/26/2016', 71.62)
insert into dbo.tblStockPrices values('Wal-Mart', '9/23/2016', 72.35)
insert into dbo.tblStockPrices values('Wal-Mart', '9/22/2016', 72.27)
insert into dbo.tblStockPrices values('Wal-Mart', '9/21/2016', 72.19)
insert into dbo.tblStockPrices values('Wal-Mart', '9/20/2016', 71.97)
insert into dbo.tblStockPrices values('Wal-Mart', '9/19/2016', 72.09)
insert into dbo.tblStockPrices values('Wal-Mart', '9/16/2016', 72.87)
insert into dbo.tblStockPrices values('Wal-Mart', '9/15/2016', 72.4)
insert into dbo.tblStockPrices values('Wal-Mart', '9/14/2016', 71.52)
insert into dbo.tblStockPrices values('Wal-Mart', '9/13/2016', 71.46)
insert into dbo.tblStockPrices values('Wal-Mart', '9/12/2016', 71.94)
insert into dbo.tblStockPrices values('Wal-Mart', '9/9/2016', 70.3)
insert into dbo.tblStockPrices values('Wal-Mart', '9/8/2016', 71.83)
insert into dbo.tblStockPrices values('Wal-Mart', '9/7/2016', 72.06)
insert into dbo.tblStockPrices values('Wal-Mart', '9/6/2016', 73)
insert into dbo.tblStockPrices values('Wal-Mart', '9/2/2016', 72.5)
insert into dbo.tblStockPrices values('Wal-Mart', '9/1/2016', 72.84)
insert into dbo.tblStockPrices values('Wal-Mart', '8/31/2016', 71.44)
insert into dbo.tblStockPrices values('Wal-Mart', '8/30/2016', 71.31)
insert into dbo.tblStockPrices values('Wal-Mart', '8/29/2016', 71.4)
insert into dbo.tblStockPrices values('Wal-Mart', '8/26/2016', 71.14)
insert into dbo.tblStockPrices values('Wal-Mart', '8/25/2016', 71.22)
insert into dbo.tblStockPrices values('Wal-Mart', '8/24/2016', 72.23)
insert into dbo.tblStockPrices values('Wal-Mart', '8/23/2016', 71.97)
insert into dbo.tblStockPrices values('Wal-Mart', '8/22/2016', 72.7)
insert into dbo.tblStockPrices values('Wal-Mart', '8/19/2016', 72.81)
insert into dbo.tblStockPrices values('Wal-Mart', '8/18/2016', 74.3)
insert into dbo.tblStockPrices values('Wal-Mart', '8/17/2016', 72.93)
insert into dbo.tblStockPrices values('Wal-Mart', '8/16/2016', 72.89)
insert into dbo.tblStockPrices values('Wal-Mart', '8/15/2016', 73.32)
insert into dbo.tblStockPrices values('Wal-Mart', '8/12/2016', 73.89)
insert into dbo.tblStockPrices values('Wal-Mart', '8/11/2016', 73.8)
insert into dbo.tblStockPrices values('Wal-Mart', '8/10/2016', 73.95)
insert into dbo.tblStockPrices values('Wal-Mart', '8/9/2016', 73.54)
insert into dbo.tblStockPrices values('Wal-Mart', '8/8/2016', 73.34)
insert into dbo.tblStockPrices values('Wal-Mart', '8/5/2016', 73.76)
insert into dbo.tblStockPrices values('Wal-Mart', '8/4/2016', 73.3)
insert into dbo.tblStockPrices values('Wal-Mart', '8/3/2016', 72.94)
insert into dbo.tblStockPrices values('Wal-Mart', '8/2/2016', 73.13)
insert into dbo.tblStockPrices values('Wal-Mart', '8/1/2016', 73.78)
insert into dbo.tblStockPrices values('Wal-Mart', '7/29/2016', 72.97)
insert into dbo.tblStockPrices values('Wal-Mart', '7/28/2016', 73.24)
insert into dbo.tblStockPrices values('Wal-Mart', '7/27/2016', 73.32)
insert into dbo.tblStockPrices values('Home Depot', '10/28/2016', 122.26)
insert into dbo.tblStockPrices values('Home Depot', '10/27/2016', 122.26)
insert into dbo.tblStockPrices values('Home Depot', '10/26/2016', 122.71)
insert into dbo.tblStockPrices values('Home Depot', '10/25/2016', 123.34)
insert into dbo.tblStockPrices values('Home Depot', '10/24/2016', 127.78)
insert into dbo.tblStockPrices values('Home Depot', '10/21/2016', 126.6)
insert into dbo.tblStockPrices values('Home Depot', '10/20/2016', 126.25)
insert into dbo.tblStockPrices values('Home Depot', '10/19/2016', 125.89)
insert into dbo.tblStockPrices values('Home Depot', '10/18/2016', 125.74)
insert into dbo.tblStockPrices values('Home Depot', '10/17/2016', 125.14)
insert into dbo.tblStockPrices values('Home Depot', '10/14/2016', 126.42)
insert into dbo.tblStockPrices values('Home Depot', '10/13/2016', 126.29)
insert into dbo.tblStockPrices values('Home Depot', '10/12/2016', 126.69)
insert into dbo.tblStockPrices values('Home Depot', '10/11/2016', 125.85)
insert into dbo.tblStockPrices values('Home Depot', '10/10/2016', 127.25)
insert into dbo.tblStockPrices values('Home Depot', '10/7/2016', 128.31)
insert into dbo.tblStockPrices values('Home Depot', '10/6/2016', 130.19)
insert into dbo.tblStockPrices values('Home Depot', '10/5/2016', 127.58)
insert into dbo.tblStockPrices values('Home Depot', '10/4/2016', 128.2)
insert into dbo.tblStockPrices values('Home Depot', '10/3/2016', 128.5)
insert into dbo.tblStockPrices values('Home Depot', '9/30/2016', 128.68)
insert into dbo.tblStockPrices values('Home Depot', '9/29/2016', 127.93)
insert into dbo.tblStockPrices values('Home Depot', '9/28/2016', 128.27)
insert into dbo.tblStockPrices values('Home Depot', '9/27/2016', 126.82)
insert into dbo.tblStockPrices values('Home Depot', '9/26/2016', 125.45)
insert into dbo.tblStockPrices values('Home Depot', '9/23/2016', 127.79)
insert into dbo.tblStockPrices values('Home Depot', '9/22/2016', 128.75)
insert into dbo.tblStockPrices values('Home Depot', '9/21/2016', 128.11)
insert into dbo.tblStockPrices values('Home Depot', '9/20/2016', 126.83)
insert into dbo.tblStockPrices values('Home Depot', '9/19/2016', 126.29)
insert into dbo.tblStockPrices values('Home Depot', '9/16/2016', 126.11)
insert into dbo.tblStockPrices values('Home Depot', '9/15/2016', 126.96)
insert into dbo.tblStockPrices values('Home Depot', '9/14/2016', 126.26)
insert into dbo.tblStockPrices values('Home Depot', '9/13/2016', 125.72)
insert into dbo.tblStockPrices values('Home Depot', '9/12/2016', 128.59)
insert into dbo.tblStockPrices values('Home Depot', '9/9/2016', 127.74)
insert into dbo.tblStockPrices values('Home Depot', '9/8/2016', 131.26)
insert into dbo.tblStockPrices values('Home Depot', '9/7/2016', 132.79)
insert into dbo.tblStockPrices values('Home Depot', '9/6/2016', 134.1)
insert into dbo.tblStockPrices values('Home Depot', '9/2/2016', 135.15)
insert into dbo.tblStockPrices values('Home Depot', '9/1/2016', 134.23)
insert into dbo.tblStockPrices values('Home Depot', '8/31/2016', 134.12)
insert into dbo.tblStockPrices values('Home Depot', '8/30/2016', 134.1)
insert into dbo.tblStockPrices values('Home Depot', '8/29/2016', 135.24)
insert into dbo.tblStockPrices values('Home Depot', '8/26/2016', 134.36)
insert into dbo.tblStockPrices values('Home Depot', '8/25/2016', 135.43)
insert into dbo.tblStockPrices values('Home Depot', '8/24/2016', 135.06)
insert into dbo.tblStockPrices values('Home Depot', '8/23/2016', 136.22)
insert into dbo.tblStockPrices values('Home Depot', '8/22/2016', 135.34)
insert into dbo.tblStockPrices values('Home Depot', '8/19/2016', 135.46)
insert into dbo.tblStockPrices values('Home Depot', '8/18/2016', 134.8)
insert into dbo.tblStockPrices values('Home Depot', '8/17/2016', 136.15)
insert into dbo.tblStockPrices values('Home Depot', '8/16/2016', 136.23)
insert into dbo.tblStockPrices values('Home Depot', '8/15/2016', 137.06)
insert into dbo.tblStockPrices values('Home Depot', '8/12/2016', 136.57)
insert into dbo.tblStockPrices values('Home Depot', '8/11/2016', 137.04)
insert into dbo.tblStockPrices values('Home Depot', '8/10/2016', 135.6)
insert into dbo.tblStockPrices values('Home Depot', '8/9/2016', 136.11)
insert into dbo.tblStockPrices values('Home Depot', '8/8/2016', 136.21)
insert into dbo.tblStockPrices values('Home Depot', '8/5/2016', 136.91)
insert into dbo.tblStockPrices values('Home Depot', '8/4/2016', 136.05)
insert into dbo.tblStockPrices values('Home Depot', '8/3/2016', 137.06)
insert into dbo.tblStockPrices values('Home Depot', '8/2/2016', 137.22)
insert into dbo.tblStockPrices values('Home Depot', '8/1/2016', 138.77)
insert into dbo.tblStockPrices values('Home Depot', '7/29/2016', 138.24)
insert into dbo.tblStockPrices values('Home Depot', '7/28/2016', 137.96)
insert into dbo.tblStockPrices values('Home Depot', '7/27/2016', 136.31)
insert into dbo.tblStockPrices values('Lowes', '10/28/2016', 67.16)
insert into dbo.tblStockPrices values('Lowes', '10/27/2016', 67.16)
insert into dbo.tblStockPrices values('Lowes', '10/26/2016', 67.37)
insert into dbo.tblStockPrices values('Lowes', '10/25/2016', 68.47)
insert into dbo.tblStockPrices values('Lowes', '10/24/2016', 70.98)
insert into dbo.tblStockPrices values('Lowes', '10/21/2016', 70.65)
insert into dbo.tblStockPrices values('Lowes', '10/20/2016', 69.89)
insert into dbo.tblStockPrices values('Lowes', '10/19/2016', 69.59)
insert into dbo.tblStockPrices values('Lowes', '10/18/2016', 70.24)
insert into dbo.tblStockPrices values('Lowes', '10/17/2016', 69.98)
insert into dbo.tblStockPrices values('Lowes', '10/14/2016', 71.35)
insert into dbo.tblStockPrices values('Lowes', '10/13/2016', 71.1)
insert into dbo.tblStockPrices values('Lowes', '10/12/2016', 71.32)
insert into dbo.tblStockPrices values('Lowes', '10/11/2016', 70.09)
insert into dbo.tblStockPrices values('Lowes', '10/10/2016', 70.97)
insert into dbo.tblStockPrices values('Lowes', '10/7/2016', 71.3)
insert into dbo.tblStockPrices values('Lowes', '10/6/2016', 72.09)
insert into dbo.tblStockPrices values('Lowes', '10/5/2016', 72.25)
insert into dbo.tblStockPrices values('Lowes', '10/4/2016', 72.7)
insert into dbo.tblStockPrices values('Lowes', '10/3/2016', 72.19)
insert into dbo.tblStockPrices values('Lowes', '9/30/2016', 72.21)
insert into dbo.tblStockPrices values('Lowes', '9/29/2016', 72.01)
insert into dbo.tblStockPrices values('Lowes', '9/28/2016', 71.76)
insert into dbo.tblStockPrices values('Lowes', '9/27/2016', 71.5)
insert into dbo.tblStockPrices values('Lowes', '9/26/2016', 70.81)
insert into dbo.tblStockPrices values('Lowes', '9/23/2016', 72.35)
insert into dbo.tblStockPrices values('Lowes', '9/22/2016', 72.08)
insert into dbo.tblStockPrices values('Lowes', '9/21/2016', 72.05)
insert into dbo.tblStockPrices values('Lowes', '9/20/2016', 71.37)
insert into dbo.tblStockPrices values('Lowes', '9/19/2016', 71.31)
insert into dbo.tblStockPrices values('Lowes', '9/16/2016', 70.95)
insert into dbo.tblStockPrices values('Lowes', '9/15/2016', 71.31)
insert into dbo.tblStockPrices values('Lowes', '9/14/2016', 71.12)
insert into dbo.tblStockPrices values('Lowes', '9/13/2016', 71.08)
insert into dbo.tblStockPrices values('Lowes', '9/12/2016', 72.5)
insert into dbo.tblStockPrices values('Lowes', '9/9/2016', 71.77)
insert into dbo.tblStockPrices values('Lowes', '9/8/2016', 74.13)
insert into dbo.tblStockPrices values('Lowes', '9/7/2016', 75.44)
insert into dbo.tblStockPrices values('Lowes', '9/6/2016', 76.01)
insert into dbo.tblStockPrices values('Lowes', '9/2/2016', 76.96)
insert into dbo.tblStockPrices values('Lowes', '9/1/2016', 76.44)
insert into dbo.tblStockPrices values('Lowes', '8/31/2016', 76.56)
insert into dbo.tblStockPrices values('Lowes', '8/30/2016', 76.56)
insert into dbo.tblStockPrices values('Lowes', '8/29/2016', 77.18)
insert into dbo.tblStockPrices values('Lowes', '8/26/2016', 76.9)
insert into dbo.tblStockPrices values('Lowes', '8/25/2016', 77.47)
insert into dbo.tblStockPrices values('Lowes', '8/24/2016', 76.96)
insert into dbo.tblStockPrices values('Lowes', '8/23/2016', 77.3)
insert into dbo.tblStockPrices values('Lowes', '8/22/2016', 77.29)
insert into dbo.tblStockPrices values('Lowes', '8/19/2016', 77.82)
insert into dbo.tblStockPrices values('Lowes', '8/18/2016', 76.43)
insert into dbo.tblStockPrices values('Lowes', '8/17/2016', 76.88)
insert into dbo.tblStockPrices values('Lowes', '8/16/2016', 81.48)
insert into dbo.tblStockPrices values('Lowes', '8/15/2016', 81.54)
insert into dbo.tblStockPrices values('Lowes', '8/12/2016', 81.72)
insert into dbo.tblStockPrices values('Lowes', '8/11/2016', 81.61)
insert into dbo.tblStockPrices values('Lowes', '8/10/2016', 80.98)
insert into dbo.tblStockPrices values('Lowes', '8/9/2016', 81.34)
insert into dbo.tblStockPrices values('Lowes', '8/8/2016', 81.62)
insert into dbo.tblStockPrices values('Lowes', '8/5/2016', 81.72)
insert into dbo.tblStockPrices values('Lowes', '8/4/2016', 80.94)
insert into dbo.tblStockPrices values('Lowes', '8/3/2016', 82.02)
insert into dbo.tblStockPrices values('Lowes', '8/2/2016', 81.97)
insert into dbo.tblStockPrices values('Lowes', '8/1/2016', 82.31)
insert into dbo.tblStockPrices values('Lowes', '7/29/2016', 82.28)
insert into dbo.tblStockPrices values('Lowes', '7/28/2016', 82.27)
insert into dbo.tblStockPrices values('Lowes', '7/27/2016', 80.76)
insert into dbo.tblStockPrices values('Symantec', '10/28/2016', 24.93)
insert into dbo.tblStockPrices values('Symantec', '10/27/2016', 24.93)
insert into dbo.tblStockPrices values('Symantec', '10/26/2016', 25.03)
insert into dbo.tblStockPrices values('Symantec', '10/25/2016', 24.99)
insert into dbo.tblStockPrices values('Symantec', '10/24/2016', 24.9)
insert into dbo.tblStockPrices values('Symantec', '10/21/2016', 24.08)
insert into dbo.tblStockPrices values('Symantec', '10/20/2016', 24.2)
insert into dbo.tblStockPrices values('Symantec', '10/19/2016', 24.23)
insert into dbo.tblStockPrices values('Symantec', '10/18/2016', 24.22)
insert into dbo.tblStockPrices values('Symantec', '10/17/2016', 24.19)
insert into dbo.tblStockPrices values('Symantec', '10/14/2016', 24.09)
insert into dbo.tblStockPrices values('Symantec', '10/13/2016', 24.28)
insert into dbo.tblStockPrices values('Symantec', '10/12/2016', 24.94)
insert into dbo.tblStockPrices values('Symantec', '10/11/2016', 25.06)
insert into dbo.tblStockPrices values('Symantec', '10/10/2016', 25.25)
insert into dbo.tblStockPrices values('Symantec', '10/7/2016', 25.21)
insert into dbo.tblStockPrices values('Symantec', '10/6/2016', 25.3)
insert into dbo.tblStockPrices values('Symantec', '10/5/2016', 25.39)
insert into dbo.tblStockPrices values('Symantec', '10/4/2016', 25.25)
insert into dbo.tblStockPrices values('Symantec', '10/3/2016', 25.11)
insert into dbo.tblStockPrices values('Symantec', '9/30/2016', 25.1)
insert into dbo.tblStockPrices values('Symantec', '9/29/2016', 25.01)
insert into dbo.tblStockPrices values('Symantec', '9/28/2016', 25.27)
insert into dbo.tblStockPrices values('Symantec', '9/27/2016', 25.16)
insert into dbo.tblStockPrices values('Symantec', '9/26/2016', 24.96)
insert into dbo.tblStockPrices values('Symantec', '9/23/2016', 25.04)
insert into dbo.tblStockPrices values('Symantec', '9/22/2016', 25.08)
insert into dbo.tblStockPrices values('Symantec', '9/21/2016', 25.08)
insert into dbo.tblStockPrices values('Symantec', '9/20/2016', 24.71)
insert into dbo.tblStockPrices values('Symantec', '9/19/2016', 24.84)
insert into dbo.tblStockPrices values('Symantec', '9/16/2016', 25.21)
insert into dbo.tblStockPrices values('Symantec', '9/15/2016', 24.87)
insert into dbo.tblStockPrices values('Symantec', '9/14/2016', 24.58)
insert into dbo.tblStockPrices values('Symantec', '9/13/2016', 24.69)
insert into dbo.tblStockPrices values('Symantec', '9/12/2016', 24.85)
insert into dbo.tblStockPrices values('Symantec', '9/9/2016', 24.49)
insert into dbo.tblStockPrices values('Symantec', '9/8/2016', 24.95)
insert into dbo.tblStockPrices values('Symantec', '9/7/2016', 24.92)
insert into dbo.tblStockPrices values('Symantec', '9/6/2016', 24.81)
insert into dbo.tblStockPrices values('Symantec', '9/2/2016', 24.28)
insert into dbo.tblStockPrices values('Symantec', '9/1/2016', 24.15)
insert into dbo.tblStockPrices values('Symantec', '8/31/2016', 24.13)
insert into dbo.tblStockPrices values('Symantec', '8/30/2016', 23.98)
insert into dbo.tblStockPrices values('Symantec', '8/29/2016', 24.13)
insert into dbo.tblStockPrices values('Symantec', '8/26/2016', 23.72)
insert into dbo.tblStockPrices values('Symantec', '8/25/2016', 23.73)
insert into dbo.tblStockPrices values('Symantec', '8/24/2016', 23.76)
insert into dbo.tblStockPrices values('Symantec', '8/23/2016', 23.87)
insert into dbo.tblStockPrices values('Symantec', '8/22/2016', 23.74)
insert into dbo.tblStockPrices values('Symantec', '8/19/2016', 23.72)
insert into dbo.tblStockPrices values('Symantec', '8/18/2016', 23.11)
insert into dbo.tblStockPrices values('Symantec', '8/17/2016', 22.92)
insert into dbo.tblStockPrices values('Symantec', '8/16/2016', 22.98)
insert into dbo.tblStockPrices values('Symantec', '8/15/2016', 22.88)
insert into dbo.tblStockPrices values('Symantec', '8/12/2016', 22.54)
insert into dbo.tblStockPrices values('Symantec', '8/11/2016', 22.11)
insert into dbo.tblStockPrices values('Symantec', '8/10/2016', 21.73)
insert into dbo.tblStockPrices values('Symantec', '8/9/2016', 21.66)
insert into dbo.tblStockPrices values('Symantec', '8/8/2016', 21.78)
insert into dbo.tblStockPrices values('Symantec', '8/5/2016', 21.885)
insert into dbo.tblStockPrices values('Symantec', '8/4/2016', 21.03)
insert into dbo.tblStockPrices values('Symantec', '8/3/2016', 20.9)
insert into dbo.tblStockPrices values('Symantec', '8/2/2016', 20.68)
insert into dbo.tblStockPrices values('Symantec', '8/1/2016', 20.55)
insert into dbo.tblStockPrices values('Symantec', '7/29/2016', 20.43)
insert into dbo.tblStockPrices values('Symantec', '7/28/2016', 20.45)
insert into dbo.tblStockPrices values('Symantec', '7/27/2016', 20.29)
insert into dbo.tblStockPrices values('Cisco', '10/28/2016', 30.38)
insert into dbo.tblStockPrices values('Cisco', '10/27/2016', 30.38)
insert into dbo.tblStockPrices values('Cisco', '10/26/2016', 30.55)
insert into dbo.tblStockPrices values('Cisco', '10/25/2016', 30.34)
insert into dbo.tblStockPrices values('Cisco', '10/24/2016', 30.46)
insert into dbo.tblStockPrices values('Cisco', '10/21/2016', 30.15)
insert into dbo.tblStockPrices values('Cisco', '10/20/2016', 30.16)
insert into dbo.tblStockPrices values('Cisco', '10/19/2016', 30.35)
insert into dbo.tblStockPrices values('Cisco', '10/18/2016', 30.44)
insert into dbo.tblStockPrices values('Cisco', '10/17/2016', 30.22)
insert into dbo.tblStockPrices values('Cisco', '10/14/2016', 30.18)
insert into dbo.tblStockPrices values('Cisco', '10/13/2016', 30.17)
insert into dbo.tblStockPrices values('Cisco', '10/12/2016', 30.34)
insert into dbo.tblStockPrices values('Cisco', '10/11/2016', 31.04)
insert into dbo.tblStockPrices values('Cisco', '10/10/2016', 31.47)
insert into dbo.tblStockPrices values('Cisco', '10/7/2016', 31.47)
insert into dbo.tblStockPrices values('Cisco', '10/6/2016', 31.48)
insert into dbo.tblStockPrices values('Cisco', '10/5/2016', 31.59)
insert into dbo.tblStockPrices values('Cisco', '10/4/2016', 31.35)
insert into dbo.tblStockPrices values('Cisco', '10/3/2016', 31.5)
insert into dbo.tblStockPrices values('Cisco', '9/30/2016', 31.72)
insert into dbo.tblStockPrices values('Cisco', '9/29/2016', 31.39)
insert into dbo.tblStockPrices values('Cisco', '9/28/2016', 31.5)
insert into dbo.tblStockPrices values('Cisco', '9/27/2016', 31.48)
insert into dbo.tblStockPrices values('Cisco', '9/26/2016', 31.07)
insert into dbo.tblStockPrices values('Cisco', '9/23/2016', 31.34)
insert into dbo.tblStockPrices values('Cisco', '9/22/2016', 31.66)
insert into dbo.tblStockPrices values('Cisco', '9/21/2016', 31.36)
insert into dbo.tblStockPrices values('Cisco', '9/20/2016', 31.1)
insert into dbo.tblStockPrices values('Cisco', '9/19/2016', 31.02)
insert into dbo.tblStockPrices values('Cisco', '9/16/2016', 30.84)
insert into dbo.tblStockPrices values('Cisco', '9/15/2016', 31.31)
insert into dbo.tblStockPrices values('Cisco', '9/14/2016', 31)
insert into dbo.tblStockPrices values('Cisco', '9/13/2016', 31.06)
insert into dbo.tblStockPrices values('Cisco', '9/12/2016', 31.44)
insert into dbo.tblStockPrices values('Cisco', '9/9/2016', 30.85)
insert into dbo.tblStockPrices values('Cisco', '9/8/2016', 31.47)
insert into dbo.tblStockPrices values('Cisco', '9/7/2016', 31.79)
insert into dbo.tblStockPrices values('Cisco', '9/6/2016', 31.87)
insert into dbo.tblStockPrices values('Cisco', '9/2/2016', 31.83)
insert into dbo.tblStockPrices values('Cisco', '9/1/2016', 31.58)
insert into dbo.tblStockPrices values('Cisco', '8/31/2016', 31.44)
insert into dbo.tblStockPrices values('Cisco', '8/30/2016', 31.54)
insert into dbo.tblStockPrices values('Cisco', '8/29/2016', 31.58)
insert into dbo.tblStockPrices values('Cisco', '8/26/2016', 31.35)
insert into dbo.tblStockPrices values('Cisco', '8/25/2016', 31.29)
insert into dbo.tblStockPrices values('Cisco', '8/24/2016', 31.06)
insert into dbo.tblStockPrices values('Cisco', '8/23/2016', 30.98)
insert into dbo.tblStockPrices values('Cisco', '8/22/2016', 30.63)
insert into dbo.tblStockPrices values('Cisco', '8/19/2016', 30.52)
insert into dbo.tblStockPrices values('Cisco', '8/18/2016', 30.48)
insert into dbo.tblStockPrices values('Cisco', '8/17/2016', 30.72)
insert into dbo.tblStockPrices values('Cisco', '8/16/2016', 31.12)
insert into dbo.tblStockPrices values('Cisco', '8/15/2016', 31.19)
insert into dbo.tblStockPrices values('Cisco', '8/12/2016', 30.87)
insert into dbo.tblStockPrices values('Cisco', '8/11/2016', 30.95)
insert into dbo.tblStockPrices values('Cisco', '8/10/2016', 30.85)
insert into dbo.tblStockPrices values('Cisco', '8/9/2016', 30.94)
insert into dbo.tblStockPrices values('Cisco', '8/8/2016', 31.01)
insert into dbo.tblStockPrices values('Cisco', '8/5/2016', 31.04)
insert into dbo.tblStockPrices values('Cisco', '8/4/2016', 30.8)
insert into dbo.tblStockPrices values('Cisco', '8/3/2016', 30.72)
insert into dbo.tblStockPrices values('Cisco', '8/2/2016', 30.62)
insert into dbo.tblStockPrices values('Cisco', '8/1/2016', 30.73)
insert into dbo.tblStockPrices values('Cisco', '7/29/2016', 30.53)
insert into dbo.tblStockPrices values('Cisco', '7/28/2016', 30.52)
insert into dbo.tblStockPrices values('Cisco', '7/27/2016', 30.76)
insert into dbo.tblStockPrices values('GoPro', '10/28/2016', 13.73)
insert into dbo.tblStockPrices values('GoPro', '10/27/2016', 13.73)
insert into dbo.tblStockPrices values('GoPro', '10/26/2016', 14.13)
insert into dbo.tblStockPrices values('GoPro', '10/25/2016', 13.88)
insert into dbo.tblStockPrices values('GoPro', '10/24/2016', 13.88)
insert into dbo.tblStockPrices values('GoPro', '10/21/2016', 14.93)
insert into dbo.tblStockPrices values('GoPro', '10/20/2016', 14.75)
insert into dbo.tblStockPrices values('GoPro', '10/19/2016', 14.7)
insert into dbo.tblStockPrices values('GoPro', '10/18/2016', 14.41)
insert into dbo.tblStockPrices values('GoPro', '10/17/2016', 14.07)
insert into dbo.tblStockPrices values('GoPro', '10/14/2016', 13.6)
insert into dbo.tblStockPrices values('GoPro', '10/13/2016', 13.84)
insert into dbo.tblStockPrices values('GoPro', '10/12/2016', 14.3)
insert into dbo.tblStockPrices values('GoPro', '10/11/2016', 15.02)
insert into dbo.tblStockPrices values('GoPro', '10/10/2016', 15.66)
insert into dbo.tblStockPrices values('GoPro', '10/7/2016', 16.34)
insert into dbo.tblStockPrices values('GoPro', '10/6/2016', 16.79)
insert into dbo.tblStockPrices values('GoPro', '10/5/2016', 17.13)
insert into dbo.tblStockPrices values('GoPro', '10/4/2016', 16.89)
insert into dbo.tblStockPrices values('GoPro', '10/3/2016', 16.74)
insert into dbo.tblStockPrices values('GoPro', '9/30/2016', 16.68)
insert into dbo.tblStockPrices values('GoPro', '9/29/2016', 16.6)
insert into dbo.tblStockPrices values('GoPro', '9/28/2016', 16.99)
insert into dbo.tblStockPrices values('GoPro', '9/27/2016', 16.79)
insert into dbo.tblStockPrices values('GoPro', '9/26/2016', 16.92)
insert into dbo.tblStockPrices values('GoPro', '9/23/2016', 17.15)
insert into dbo.tblStockPrices values('GoPro', '9/22/2016', 16.05)
insert into dbo.tblStockPrices values('GoPro', '9/21/2016', 14.97)
insert into dbo.tblStockPrices values('GoPro', '9/20/2016', 14.26)
insert into dbo.tblStockPrices values('GoPro', '9/19/2016', 15.31)
insert into dbo.tblStockPrices values('GoPro', '9/16/2016', 14.96)
insert into dbo.tblStockPrices values('GoPro', '9/15/2016', 14.16)
insert into dbo.tblStockPrices values('GoPro', '9/14/2016', 13.07)
insert into dbo.tblStockPrices values('GoPro', '9/13/2016', 12.8)
insert into dbo.tblStockPrices values('GoPro', '9/12/2016', 13.2)
insert into dbo.tblStockPrices values('GoPro', '9/9/2016', 12.85)
insert into dbo.tblStockPrices values('GoPro', '9/8/2016', 13.58)
insert into dbo.tblStockPrices values('GoPro', '9/7/2016', 13.88)
insert into dbo.tblStockPrices values('GoPro', '9/6/2016', 14.57)
insert into dbo.tblStockPrices values('GoPro', '9/2/2016', 14.13)
insert into dbo.tblStockPrices values('GoPro', '9/1/2016', 14.53)
insert into dbo.tblStockPrices values('GoPro', '8/31/2016', 14.65)
insert into dbo.tblStockPrices values('GoPro', '8/30/2016', 14.86)
insert into dbo.tblStockPrices values('GoPro', '8/29/2016', 14.87)
insert into dbo.tblStockPrices values('GoPro', '8/26/2016', 14.72)
insert into dbo.tblStockPrices values('GoPro', '8/25/2016', 14.89)
insert into dbo.tblStockPrices values('GoPro', '8/24/2016', 15)
insert into dbo.tblStockPrices values('GoPro', '8/23/2016', 15.51)
insert into dbo.tblStockPrices values('GoPro', '8/22/2016', 15.2)
insert into dbo.tblStockPrices values('GoPro', '8/19/2016', 15.12)
insert into dbo.tblStockPrices values('GoPro', '8/18/2016', 15.12)
insert into dbo.tblStockPrices values('GoPro', '8/17/2016', 15.41)
insert into dbo.tblStockPrices values('GoPro', '8/16/2016', 15.39)
insert into dbo.tblStockPrices values('GoPro', '8/15/2016', 15.61)
insert into dbo.tblStockPrices values('GoPro', '8/12/2016', 14.3)
insert into dbo.tblStockPrices values('GoPro', '8/11/2016', 13.97)
insert into dbo.tblStockPrices values('GoPro', '8/10/2016', 14.14)
insert into dbo.tblStockPrices values('GoPro', '8/9/2016', 14.54)
insert into dbo.tblStockPrices values('GoPro', '8/8/2016', 14.29)
insert into dbo.tblStockPrices values('GoPro', '8/5/2016', 13.77)
insert into dbo.tblStockPrices values('GoPro', '8/4/2016', 13.53)
insert into dbo.tblStockPrices values('GoPro', '8/3/2016', 13.3)
insert into dbo.tblStockPrices values('GoPro', '8/2/2016', 12.55)
insert into dbo.tblStockPrices values('GoPro', '8/1/2016', 12.94)
insert into dbo.tblStockPrices values('GoPro', '7/29/2016', 12.64)
insert into dbo.tblStockPrices values('GoPro', '7/28/2016', 13.02)
insert into dbo.tblStockPrices values('GoPro', '7/27/2016', 11.57)
insert into dbo.tblStockPrices values('Google', '10/28/2016', 817.35)
insert into dbo.tblStockPrices values('Google', '10/27/2016', 817.35)
insert into dbo.tblStockPrices values('Google', '10/26/2016', 822.1)
insert into dbo.tblStockPrices values('Google', '10/25/2016', 828.55)
insert into dbo.tblStockPrices values('Google', '10/24/2016', 835.74)
insert into dbo.tblStockPrices values('Google', '10/21/2016', 824.06)
insert into dbo.tblStockPrices values('Google', '10/20/2016', 821.63)
insert into dbo.tblStockPrices values('Google', '10/19/2016', 827.09)
insert into dbo.tblStockPrices values('Google', '10/18/2016', 821.49)
insert into dbo.tblStockPrices values('Google', '10/17/2016', 806.84)
insert into dbo.tblStockPrices values('Google', '10/14/2016', 804.6)
insert into dbo.tblStockPrices values('Google', '10/13/2016', 804.08)
insert into dbo.tblStockPrices values('Google', '10/12/2016', 811.77)
insert into dbo.tblStockPrices values('Google', '10/11/2016', 809.57)
insert into dbo.tblStockPrices values('Google', '10/10/2016', 814.17)
insert into dbo.tblStockPrices values('Google', '10/7/2016', 800.71)
insert into dbo.tblStockPrices values('Google', '10/6/2016', 803.08)
insert into dbo.tblStockPrices values('Google', '10/5/2016', 801.23)
insert into dbo.tblStockPrices values('Google', '10/4/2016', 802.79)
insert into dbo.tblStockPrices values('Google', '10/3/2016', 800.38)
insert into dbo.tblStockPrices values('Google', '9/30/2016', 804.06)
insert into dbo.tblStockPrices values('Google', '9/29/2016', 802.64)
insert into dbo.tblStockPrices values('Google', '9/28/2016', 810.06)
insert into dbo.tblStockPrices values('Google', '9/27/2016', 810.73)
insert into dbo.tblStockPrices values('Google', '9/26/2016', 802.65)
insert into dbo.tblStockPrices values('Google', '9/23/2016', 814.96)
insert into dbo.tblStockPrices values('Google', '9/22/2016', 815.95)
insert into dbo.tblStockPrices values('Google', '9/21/2016', 805.03)
insert into dbo.tblStockPrices values('Google', '9/20/2016', 799.78)
insert into dbo.tblStockPrices values('Google', '9/19/2016', 795.39)
insert into dbo.tblStockPrices values('Google', '9/16/2016', 797.97)
insert into dbo.tblStockPrices values('Google', '9/15/2016', 801.23)
insert into dbo.tblStockPrices values('Google', '9/14/2016', 790.46)
insert into dbo.tblStockPrices values('Google', '9/13/2016', 788.72)
insert into dbo.tblStockPrices values('Google', '9/12/2016', 798.82)
insert into dbo.tblStockPrices values('Google', '9/9/2016', 788.48)
insert into dbo.tblStockPrices values('Google', '9/8/2016', 802.84)
insert into dbo.tblStockPrices values('Google', '9/7/2016', 807.99)
insert into dbo.tblStockPrices values('Google', '9/6/2016', 808.02)
insert into dbo.tblStockPrices values('Google', '9/2/2016', 796.87)
insert into dbo.tblStockPrices values('Google', '9/1/2016', 791.4)
insert into dbo.tblStockPrices values('Google', '8/31/2016', 789.85)
insert into dbo.tblStockPrices values('Google', '8/30/2016', 791.92)
insert into dbo.tblStockPrices values('Google', '8/29/2016', 795.82)
insert into dbo.tblStockPrices values('Google', '8/26/2016', 793.22)
insert into dbo.tblStockPrices values('Google', '8/25/2016', 791.3)
insert into dbo.tblStockPrices values('Google', '8/24/2016', 793.6)
insert into dbo.tblStockPrices values('Google', '8/23/2016', 796.59)
insert into dbo.tblStockPrices values('Google', '8/22/2016', 796.95)
insert into dbo.tblStockPrices values('Google', '8/19/2016', 799.65)
insert into dbo.tblStockPrices values('Google', '8/18/2016', 802.75)
insert into dbo.tblStockPrices values('Google', '8/17/2016', 805.42)
insert into dbo.tblStockPrices values('Google', '8/16/2016', 801.19)
insert into dbo.tblStockPrices values('Google', '8/15/2016', 805.96)
insert into dbo.tblStockPrices values('Google', '8/12/2016', 807.05)
insert into dbo.tblStockPrices values('Google', '8/11/2016', 808.2)
insert into dbo.tblStockPrices values('Google', '8/10/2016', 808.49)
insert into dbo.tblStockPrices values('Google', '8/9/2016', 807.48)
insert into dbo.tblStockPrices values('Google', '8/8/2016', 805.23)
insert into dbo.tblStockPrices values('Google', '8/5/2016', 806.93)
insert into dbo.tblStockPrices values('Google', '8/4/2016', 797.25)
insert into dbo.tblStockPrices values('Google', '8/3/2016', 798.92)
insert into dbo.tblStockPrices values('Google', '8/2/2016', 800.12)
insert into dbo.tblStockPrices values('Google', '8/1/2016', 800.94)
insert into dbo.tblStockPrices values('Google', '7/29/2016', 791.34)
insert into dbo.tblStockPrices values('Google', '7/28/2016', 765.84)
insert into dbo.tblStockPrices values('Google', '7/27/2016', 761.97)
insert into dbo.tblStockPrices values('Microsoft', '10/28/2016', 60.1)
insert into dbo.tblStockPrices values('Microsoft', '10/27/2016', 60.1)
insert into dbo.tblStockPrices values('Microsoft', '10/26/2016', 60.63)
insert into dbo.tblStockPrices values('Microsoft', '10/25/2016', 60.99)
insert into dbo.tblStockPrices values('Microsoft', '10/24/2016', 61)
insert into dbo.tblStockPrices values('Microsoft', '10/21/2016', 59.66)
insert into dbo.tblStockPrices values('Microsoft', '10/20/2016', 57.25)
insert into dbo.tblStockPrices values('Microsoft', '10/19/2016', 57.53)
insert into dbo.tblStockPrices values('Microsoft', '10/18/2016', 57.66)
insert into dbo.tblStockPrices values('Microsoft', '10/17/2016', 57.22)
insert into dbo.tblStockPrices values('Microsoft', '10/14/2016', 57.42)
insert into dbo.tblStockPrices values('Microsoft', '10/13/2016', 56.92)
insert into dbo.tblStockPrices values('Microsoft', '10/12/2016', 57.11)
insert into dbo.tblStockPrices values('Microsoft', '10/11/2016', 57.19)
insert into dbo.tblStockPrices values('Microsoft', '10/10/2016', 58.04)
insert into dbo.tblStockPrices values('Microsoft', '10/7/2016', 57.8)
insert into dbo.tblStockPrices values('Microsoft', '10/6/2016', 57.74)
insert into dbo.tblStockPrices values('Microsoft', '10/5/2016', 57.64)
insert into dbo.tblStockPrices values('Microsoft', '10/4/2016', 57.24)
insert into dbo.tblStockPrices values('Microsoft', '10/3/2016', 57.42)
insert into dbo.tblStockPrices values('Microsoft', '9/30/2016', 57.6)
insert into dbo.tblStockPrices values('Microsoft', '9/29/2016', 57.4)
insert into dbo.tblStockPrices values('Microsoft', '9/28/2016', 58.03)
insert into dbo.tblStockPrices values('Microsoft', '9/27/2016', 57.95)
insert into dbo.tblStockPrices values('Microsoft', '9/26/2016', 56.9)
insert into dbo.tblStockPrices values('Microsoft', '9/23/2016', 57.43)
insert into dbo.tblStockPrices values('Microsoft', '9/22/2016', 57.82)
insert into dbo.tblStockPrices values('Microsoft', '9/21/2016', 57.76)
insert into dbo.tblStockPrices values('Microsoft', '9/20/2016', 56.81)
insert into dbo.tblStockPrices values('Microsoft', '9/19/2016', 56.93)
insert into dbo.tblStockPrices values('Microsoft', '9/16/2016', 57.25)
insert into dbo.tblStockPrices values('Microsoft', '9/15/2016', 57.19)
insert into dbo.tblStockPrices values('Microsoft', '9/14/2016', 56.26)
insert into dbo.tblStockPrices values('Microsoft', '9/13/2016', 56.53)
insert into dbo.tblStockPrices values('Microsoft', '9/12/2016', 57.05)
insert into dbo.tblStockPrices values('Microsoft', '9/9/2016', 56.21)
insert into dbo.tblStockPrices values('Microsoft', '9/8/2016', 57.43)
insert into dbo.tblStockPrices values('Microsoft', '9/7/2016', 57.66)
insert into dbo.tblStockPrices values('Microsoft', '9/6/2016', 57.61)
insert into dbo.tblStockPrices values('Microsoft', '9/2/2016', 57.67)
insert into dbo.tblStockPrices values('Microsoft', '9/1/2016', 57.59)
insert into dbo.tblStockPrices values('Microsoft', '8/31/2016', 57.46)
insert into dbo.tblStockPrices values('Microsoft', '8/30/2016', 57.89)
insert into dbo.tblStockPrices values('Microsoft', '8/29/2016', 58.1)
insert into dbo.tblStockPrices values('Microsoft', '8/26/2016', 58.03)
insert into dbo.tblStockPrices values('Microsoft', '8/25/2016', 58.17)
insert into dbo.tblStockPrices values('Microsoft', '8/24/2016', 57.95)
insert into dbo.tblStockPrices values('Microsoft', '8/23/2016', 57.89)
insert into dbo.tblStockPrices values('Microsoft', '8/22/2016', 57.67)
insert into dbo.tblStockPrices values('Microsoft', '8/19/2016', 57.62)
insert into dbo.tblStockPrices values('Microsoft', '8/18/2016', 57.6)
insert into dbo.tblStockPrices values('Microsoft', '8/17/2016', 57.56)
insert into dbo.tblStockPrices values('Microsoft', '8/16/2016', 57.44)
insert into dbo.tblStockPrices values('Microsoft', '8/15/2016', 58.12)
insert into dbo.tblStockPrices values('Microsoft', '8/12/2016', 57.94)
insert into dbo.tblStockPrices values('Microsoft', '8/11/2016', 58.3)
insert into dbo.tblStockPrices values('Microsoft', '8/10/2016', 58.02)
insert into dbo.tblStockPrices values('Microsoft', '8/9/2016', 58.2)
insert into dbo.tblStockPrices values('Microsoft', '8/8/2016', 58.06)
insert into dbo.tblStockPrices values('Microsoft', '8/5/2016', 57.96)
insert into dbo.tblStockPrices values('Microsoft', '8/4/2016', 57.39)
insert into dbo.tblStockPrices values('Microsoft', '8/3/2016', 56.97)
insert into dbo.tblStockPrices values('Microsoft', '8/2/2016', 56.58)
insert into dbo.tblStockPrices values('Microsoft', '8/1/2016', 56.58)
insert into dbo.tblStockPrices values('Microsoft', '7/29/2016', 56.68)
insert into dbo.tblStockPrices values('Microsoft', '7/28/2016', 56.21)
insert into dbo.tblStockPrices values('Microsoft', '7/27/2016', 56.19)
Next, we will run the T-SQL below with a CROSS JOIN to generate the combinations of stocks and insert these combinations into a new table. We are not interested in calculating the Pearson coefficient between a stock and itself, thus we use the expression t2.StockName>t1.StockName in the where clause. We will also use the expression t1.ClosingDate=t2.ClosingDate to ensure that duplicate records are not in our result set.
select t1.StockName as StockName1, t1.ClosingPrice as ClosingPrice1, t2.StockName as StockName2, t2.ClosingPrice as ClosingPrice2, t1.ClosingDate as ClosingDate into dbo.tblCrossJoinedStockPrices from dbo.tblStockPrices t1 cross join dbo.tblStockPrices t2 where t2.StockName>t1.StockName and t1.ClosingDate=t2.ClosingDate order by t1.StockName, t2.StockName, t1.ClosingDate select count(*) from dbo.tblCrossJoinedStockPrices
The image below shows the results from the count query above.
We will run a query to examine the records in the tblCrossJoinedStockPrices table.
Next, we will execute our Pearson correlation query. We will group by the stock names. Sorting by the descending Pearson value will have the most strongly positively correlated stocks at the top of the results with the most negatively correlated stocks at the bottom of the result set.
select StockName1, StockName2, (Avg(ClosingPrice1 * ClosingPrice2) - (Avg(ClosingPrice1) * Avg(ClosingPrice2))) / (StDevP(ClosingPrice1) * StDevP(ClosingPrice2)) as PearsonCoefficient from dbo.tblCrossJoinedStockPrices group by StockName1, StockName2 order by PearsonCoefficient desc
The results from the above query are shown below. Notice how the retailers' stocks are at the top of the result set, indicating that their stock prices moved together.
Sorting in ascending order puts the most negatively correlated stock prices at the top of the result set. Notice how the retailers' and tech companies' stock prices were the most negatively correlated.
We can plot the values of the most positively correlated data in an Excel scatterplot with a trend line to verify the prices trend together.
We can also plot values of the most negatively correlated data in an Excel scatterplot with a trend line to verify the prices trend oppositely.
Next Steps
The CROSS JOIN example shown in this tip can help save a lot of time by putting together all unique combinations of two sets of data contained in one table.
Please experiment with how different data values affect the Pearson value. Also, please check out these other tips on mathematical functions in T-SQL on MSSQLTips.com.
- SQL Server T-SQL Code to Calculate a Moving Average
- Avoiding erroneous results when using T-SQL Trigonometric Functions in SQL Server 2012
- Transforming Cartesian Coordinates to Spherical Coordinates in SQL Server with T-SQL
- Using T-SQL to Perform Z-Score Column Normalization in SQL Server
- Plotting a Bell Curve in SQL Server Reporting Services
About the author
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-12-13
