public int getStockData(SqlHandler handle, string stock, DateTime startDt, DateTime endDt)
{
try
{
string yahooURL = @"http://ichart.finance.yahoo.com/table.csv?s=" + stock
+ "&a=" + (startDt.Month - 1).ToString()
+ "&b=" + startDt.Day.ToString()
+ "&c=" + startDt.Year.ToString()
+ "&d=" + (endDt.Month - 1).ToString()
+ "&e=" + endDt.Day.ToString()
+ "&f=" + endDt.Year.ToString()
+ "&g=d&ignore=.csv";
HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(yahooURL);
HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);
string stk_data = strm.ReadToEnd();
char[] newLine = { '\n' };
char[] comma = { ',' };
string[] stk_lines = stk_data.Split(newLine, StringSplitOptions.RemoveEmptyEntries);
string insertCmd = "";
int i = 0;
foreach (string line in stk_lines)
{
i++;
if (line.Contains("Date"))
{
continue;
}
// Console.WriteLine(line);
string[] fields = line.Split(comma, StringSplitOptions.RemoveEmptyEntries);
insertCmd += "INSERT INTO stock_tbl VALUES ('" + stock + "'" + ",";
insertCmd += "'" + fields[0] + "',";
insertCmd += fields[6] + ")\n";
// Console.WriteLine(cmd);
if (i % 10 == 0)
{
// insert in batches
handle.executeNonQuery(insertCmd);
insertCmd = "";
}
}
if (insertCmd != "")
{
// insert remaining
handle.executeNonQuery(insertCmd);
}
}
catch (Exception e)
{
//Console.WriteLine(stock + " : " + e.Message);
return(-1);
}
return(0);
}
static void calculateAndStoreBetas(SqlHandler sqlHandle, string stock, List <StockData> data, int rollingWindow)
{
string insertBetasCmd = "";
if (data.Count <= rollingWindow)
{
// no history
return;
}
for (int i = rollingWindow + 1; i < data.Count; ++i)
{
bool stockPriceExistsAllDates = true;
List <double> mktReturns = new List <double>();
List <double> stkReturns = new List <double>();
// history
for (int j = i - rollingWindow; j < i; ++j)
{
if (!data[j].get_stkprc().HasValue || !data[j - 1].get_stkprc().HasValue ||
data[j].get_stkprc().Value == 0 || data[j - 1].get_stkprc().Value == 0)
{
// cannot calculate returns and hence cannot calculate beta
stockPriceExistsAllDates = false;
break;
}
double stkRet = (data[j].get_stkprc().Value - data[j - 1].get_stkprc().Value) / data[j - 1].get_stkprc().Value;
double mktRet = (data[j].get_mktprc() - data[j - 1].get_mktprc()) / data[j - 1].get_mktprc();
stkReturns.Add(stkRet);
mktReturns.Add(mktRet);
}
if (stockPriceExistsAllDates)
{
double covar = StatMath.covariance(mktReturns, stkReturns);
double beta = covar / StatMath.variance(mktReturns);
insertBetasCmd += "INSERT INTO betas_tbl VALUES ('" + stock + "', ";
insertBetasCmd += "'" + data[i].get_dt().Year.ToString()
+ "-" + data[i].get_dt().Month.ToString()
+ "-" + data[i].get_dt().Day.ToString() + "'";
insertBetasCmd += ", " + beta.ToString();
insertBetasCmd += ", " + covar.ToString() + ")\n";
if (i % 10 == 0)
{
// insert in batches
sqlHandle.executeNonQuery(insertBetasCmd);
insertBetasCmd = "";
}
}
}
if (insertBetasCmd != "")
{
// output last remaining betas
sqlHandle.executeNonQuery(insertBetasCmd);
}
}
