//Generates stock correlation matrix from user input params: tickers and daterange
private void GenerateExcelFile(object sender, RoutedEventArgs e)
{
//harvesting user input data for further processing
string ticker = Tickers.Text;
char[] delimit = { ',' };
string[] tickerArray = ticker.Split(delimit);
DateTime start = (DateTime)Date1.SelectedDate;
DateTime end = (DateTime)Date2.SelectedDate;
//creating the correlation matrix and storing it in var data
int rows = tickerArray.Length + 1;
int columns = rows;
var data = new object[rows, columns];
//preparing the database connection proxy
DataBaseClient client = new DataBaseClient();
//matrix is filled in row by row
for (var row = 1; row <= rows; row++)
{
//first row is all ticker headers
if (row == 1)
{
for (var column = 1; column <= columns; column++)
{
if (column > 1)
{
data[row - 1, column - 1] = tickerArray[column - 2];
}
}
}
else
//all other rows contain correlation coefficents
{
decimal[] stock1 = client.getPriceArray(tickerArray[row - 2], start, end); ;
decimal[] stock2;
decimal coefficient;
for (var column = 1; column <= columns; column++)
{
if (column == 1)
{
data[row - 1, column - 1] = tickerArray[row - 2];
}
//gets price data through database proxy
//Then uses statsAPI to get coefficent
else if (column >= row)
{
if (column == row)
{
data[row - 1, column - 1] = 1;
}
else
{
stock2 = client.getPriceArray(tickerArray[column - 2], start, end);
coefficient = Statistics.correlation(stock1, stock2);
data[row - 1, column - 1] = coefficient;
}
}
}
}
}
client.Close();
// Handles creation of excel workbook and loads the data all at once
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = wb.Worksheets[1];
var startCell = ws.Cells[1, 1];
var endCell = ws.Cells[rows, columns];
var writeRange = ws.Range[startCell, endCell];
writeRange.Value2 = data;
app.Visible = true;
app.WindowState = XlWindowState.xlMaximized;
}
//getClusters button will create clusters in excel
private void getClusters(object sender, RoutedEventArgs e)
{
//start our database connection proxy
DataBaseClient client = new DataBaseClient();
DateTime d1 = (DateTime)date1.SelectedDate;
DateTime d2 = (DateTime)date2.SelectedDate;
string x_ticker = xcoor.Text;
string y_ticker = ycoor.Text;
decimal[] x_vector = client.getPriceArray(x_ticker, d1, d2);
decimal[] y_vector = client.getPriceArray(y_ticker, d1, d2);
int k = int.Parse(k_param.Text);
//generate numerical points from ticker symbols. Encode points to tickers so we can translate back.
Dictionary<Tuple<decimal, decimal>, string> tuple_ticker = new Dictionary<Tuple<decimal, decimal>, string>(tickers.Length);
Tuple<decimal, decimal>[] ticker_coords = new Tuple<decimal, decimal>[tickers.Length];
int count = 0;
foreach (string symbol in tickers)
{
decimal[] price_vector = client.getPriceArray(symbol, d1, d2);
decimal x = Statistics.correlation(x_vector, price_vector);
decimal y = Statistics.correlation(y_vector, price_vector);
Tuple<decimal, decimal> point = new Tuple<decimal, decimal>(x, y);
ticker_coords[count] = point;
tuple_ticker[point] = symbol;
count += 1;
}
// cluster the data
Dictionary<Tuple<decimal, decimal>, Tuple<decimal, decimal>[]> result = Statistics.k_means(ticker_coords, k);
//re_translate the numerical data back to ticker symbols.
//format our data object to be ready for excel
//each cluster will fill a column. first two rows of each
// column will be x and y coordinate of cluster centroid
var data = new object[tickers.Length + 2, k];
int i = 2;
int j = 0;
foreach (KeyValuePair<Tuple<decimal, decimal>, Tuple<decimal, decimal>[]> entry in result)
{
data[0, j] = entry.Key.Item1;
data[1, j] = entry.Key.Item2;
foreach (Tuple<decimal, decimal> p in entry.Value)
{
data[i, j] = tuple_ticker[p];
i += 1;
}
j += 1;
i = 2;
}
// Handles creation of excel workbook and loads the data all at once
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = wb.Worksheets[1];
var startCell = ws.Cells[1, 1];
var endCell = ws.Cells[tickers.Length + 2, k];
var writeRange = ws.Range[startCell, endCell];
writeRange.Value2 = data;
app.Visible = true;
app.WindowState = XlWindowState.xlMaximized;
}
