Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Use a CrossJoin to Calculate Multiple Pearson Correlation Coefficients in T-SQL


By:   |   Read Comments   |   Related Tips: More > T-SQL

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.

Count query results

We will run a query to examine the records in the tblCrossJoinedStockPrices table.

First 8 rows in tblCrossJoinedStockPrices

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.

Pearson query in descending order

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.

Pearson query in ascending order

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.

Plot the most positively correlated

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.

Plot the most negatively correlated
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.



Last Update:






About the author
MSSQLTips author Dallas Snider 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.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools