public UsStockCollection GetUsStocks(string tickerSymbol, int fromYear, int toYear)
{
UsStockCollection stocks = null;
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@SYMBOL", MySqlDbType.VarChar),
new MySqlParameter("@FROMYEAR", MySqlDbType.Int16),
new MySqlParameter("@TOYEAR", MySqlDbType.Int16)
};
parms[0].Value = tickerSymbol;
parms[1].Value = fromYear;
parms[2].Value = toYear;
using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_COMP_YEARS, parms))
{
if (rdr.Read())
{
//If there is one result
stocks = new UsStockCollection();
//Scroll through the results
do
{
stocks.Add(ConvertReaderToUsStockObject(rdr));
}while (rdr.Read());
}
}
return(stocks);
}
public UsStockCollection GetUsStocks(int year)
{
UsStockCollection stocks = null;
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@YEAR", MySqlDbType.Int16)
};
parms[0].Value = year;
using (MySqlConnection conn = new MySqlConnection(MySqlHelper.SV_CONN_STRING))
{
using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_YEARS, parms))
{
if (rdr.Read())
{
//If there is one result
stocks = new UsStockCollection();
//Scroll through the results
do
{
stocks.Add(ConvertReaderToUsStockObject(rdr));
}while (rdr.Read());
}
}
}
return(stocks);
}
public void RefreshSingleUsStockData(string symbol)
{
UsStockCollection stocks = ReadQuandlFromStockCsv(symbol);
foreach (UsStock stock in stocks)
{
UsStockBL.Instance.UpdateUsStock(stock);
}
Console.WriteLine("Successfully Loaded {0}", symbol);
}
public UsStockCollection ReadQuandlFromStockCsv(string symbol)
{
string url = QUANDL_COMPANY_URL_PREFIX + symbol + QUANDL_COMPANY_URL_SUFFIX_WITH_KEY;
// string url = QUANDL_COMPANY_URL_PREFIX + symbol + QUANDL_COMPANY_URL_SUFFIX_WITHOUT_KEY;
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
var reader = new StreamReader(resp.GetResponseStream());
Dictionary<string, string> columnMappings = ColumnMappingsDA.Instance.GetColumnMappings();
UsStockCollection stocks = new UsStockCollection();
List<string> columnNames = new List<string>();
List<List<string>> fieldValues = new List<List<string>>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
List<string> yearValues = new List<string>();
for (int i = 0; i < values.Length; i++)
{
yearValues.Add(values[i]);
}
fieldValues.Add(yearValues);
}
for (int i = 1; i < fieldValues.Count; i++)
{
UsStock stock = new UsStock();
stock.Symbol = symbol;
for (int j = 0; j < fieldValues[i].Count; j++)
{
if (fieldValues[0][j] == "Date")
{
PropertyInfo prop = stock.GetType().GetProperty(columnMappings[fieldValues[0][j]], BindingFlags.Public | BindingFlags.Instance);
prop.SetValue(stock, Convert.ToInt16(fieldValues[i][j].Split('-')[0]));
}
else
{
PropertyInfo prop = stock.GetType().GetProperty(columnMappings[fieldValues[0][j]], BindingFlags.Public | BindingFlags.Instance);
prop.SetValue(stock, FormatDecimal(fieldValues[i][j]));
}
}
stocks.Add(stock);
}
return stocks;
}
public UsStockCollection ReadQuandlFromStockCsv(string symbol)
{
string url = QUANDL_COMPANY_URL_PREFIX + symbol + QUANDL_COMPANY_URL_SUFFIX_WITH_KEY;
// string url = QUANDL_COMPANY_URL_PREFIX + symbol + QUANDL_COMPANY_URL_SUFFIX_WITHOUT_KEY;
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
var reader = new StreamReader(resp.GetResponseStream());
Dictionary <string, string> columnMappings = ColumnMappingsDA.Instance.GetColumnMappings();
UsStockCollection stocks = new UsStockCollection();
List <string> columnNames = new List <string>();
List <List <string> > fieldValues = new List <List <string> >();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
List <string> yearValues = new List <string>();
for (int i = 0; i < values.Length; i++)
{
yearValues.Add(values[i]);
}
fieldValues.Add(yearValues);
}
for (int i = 1; i < fieldValues.Count; i++)
{
UsStock stock = new UsStock();
stock.Symbol = symbol;
for (int j = 0; j < fieldValues[i].Count; j++)
{
if (fieldValues[0][j] == "Date")
{
PropertyInfo prop = stock.GetType().GetProperty(columnMappings[fieldValues[0][j]], BindingFlags.Public | BindingFlags.Instance);
prop.SetValue(stock, Convert.ToInt16(fieldValues[i][j].Split('-')[0]));
}
else
{
PropertyInfo prop = stock.GetType().GetProperty(columnMappings[fieldValues[0][j]], BindingFlags.Public | BindingFlags.Instance);
prop.SetValue(stock, FormatDecimal(fieldValues[i][j]));
}
}
stocks.Add(stock);
}
return(stocks);
}
public void RefreshAllUsStockData()
{
var reader = new StreamReader(File.OpenRead(@"C:\Users\Daniel\Documents\Trading Documents\QUANDL\stock_list.csv"));
//var reader = new StreamReader(File.OpenRead(@"C:\Users\Daniel\Documents\Trading Documents\QUANDL\A_COMPANIES_MISSED.csv"));
bool start = false;
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
if (!line.Contains("Ticker"))
{
string symbol = values[0];
if (symbol.Equals("WFD"))
{
start = true;
}
if (start)
{
try
{
UsStockCollection stocks = ReadQuandlFromStockCsv(symbol);
foreach (UsStock stock in stocks)
{
UsStockBL.Instance.UpdateUsStock(stock);
}
Console.WriteLine("Successfully Loaded {0}", symbol);
}
catch (Exception ex)
{
Console.WriteLine(string.Format("ERROR: DID NOT LOAD symbol: {0} {1}", symbol, ex.Message));
}
}
}
}
}
public UsStockCollection GetUsStocks(string tickerSymbol, int fromYear, int toYear)
{
UsStockCollection stocks = null;
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@SYMBOL", MySqlDbType.VarChar),
new MySqlParameter("@FROMYEAR", MySqlDbType.Int16),
new MySqlParameter("@TOYEAR", MySqlDbType.Int16)
};
parms[0].Value = tickerSymbol;
parms[1].Value = fromYear;
parms[2].Value = toYear;
using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_COMP_YEARS, parms))
{
if (rdr.Read())
{
//If there is one result
stocks = new UsStockCollection();
//Scroll through the results
do
{
stocks.Add(ConvertReaderToUsStockObject(rdr));
}
while (rdr.Read());
}
}
return stocks;
}
public UsStockCollection GetUsStocks(int year)
{
UsStockCollection stocks = null;
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@YEAR", MySqlDbType.Int16)
};
parms[0].Value = year;
using (MySqlConnection conn = new MySqlConnection(MySqlHelper.SV_CONN_STRING))
{
using (MySqlDataReader rdr = MySqlHelper.ExecuteReader(MySqlHelper.SV_CONN_STRING, SQL_SELECT_YEARS, parms))
{
if (rdr.Read())
{
//If there is one result
stocks = new UsStockCollection();
//Scroll through the results
do
{
stocks.Add(ConvertReaderToUsStockObject(rdr));
}
while (rdr.Read());
}
}
}
return stocks;
}
