//getClusters button will create clusters in excel
private void getClusters(object sender, RoutedEventArgs e)
{
DateTime d1 = date1.DisplayDate;
DateTime d2 = date2.DisplayDate;
string x_ticker = xcoor.Text;
string y_ticker = ycoor.Text;
decimal[] x_vector = DBobject.getPriceArray(x_ticker, d1, d2);
decimal[] y_vector = DBobject.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 = DBobject.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;
}
//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 = Date1.DisplayDate;
DateTime end = Date2.DisplayDate;
//creating the correlation matrix and storing it in var data
int rows = tickerArray.Length + 1;
int columns = rows;
var data = new object[rows, columns];
//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
{
for (var column = 1; column <= columns; column++)
{
if (column == 1)
{
data[row - 1, column - 1] = tickerArray[row - 2];
}
//makes gets price data through DbAPI
//Then uses statsAPI to get coefficent
else if (column >= row)
{
decimal[] stock1 = DBobject.getPriceArray(tickerArray[row - 2], start, end);
decimal[] stock2 = DBobject.getPriceArray(tickerArray[column - 2], start, end);
decimal coefficient = Statistics.correlation(stock1, stock2);
data[row - 1, column - 1] = coefficient;
}
}
}
}
// 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;
}