public void Upload(YahooEODData YahooData)
{
//connect to server
string ConnectionString = "Data Source=HAO-PC\\SQLEXPRESS;Initial Catalog=Live;Integrated Security=True";
SqlConnection dbconnection = new SqlConnection(ConnectionString);
dbconnection.Open();
// sql command
SqlCommand cmd = new SqlCommand("INSERT INTO YahooEODPrice (Date, Ticker,OpenPrice,HighPrice, LowPrice, ClosePrice, Volume, AdjPrice)" +
"VALUES (@Date, @Ticker,@OpenPrice,@HighPrice, @LowPrice, @ClosePrice, @Volume, @AdjPrice)");
//upload data
cmd.CommandType = CommandType.Text;
cmd.Connection = dbconnection;
cmd.Parameters.AddWithValue("@Date", YahooData.Date);
cmd.Parameters.AddWithValue("@Ticker", YahooData.ID );
cmd.Parameters.AddWithValue("@OpenPrice", YahooData.OpenPrice);
cmd.Parameters.AddWithValue("@HighPrice", YahooData.HighPrice);
cmd.Parameters.AddWithValue("@LowPrice", YahooData.LowhPrice);
cmd.Parameters.AddWithValue("@ClosePrice", YahooData.ClosePrice);
cmd.Parameters.AddWithValue("@Volume", YahooData.Volume);
cmd.Parameters.AddWithValue("@AdjPrice", YahooData.AdjPrice);
cmd.ExecuteNonQuery();
cmd.Dispose();
dbconnection.Close();
}
//method
private static YahooEODData Parse(string csvData)
{
YahooEODData EODData = new YahooEODData();
string[] rows = csvData.Replace("\r", "").Split('\n');
int i = 1;
foreach (string row in rows)
{
if (string.IsNullOrEmpty(row))
{
continue;
}
string[] cols = row.Split(',');
if (i != 1)
{
EODData.Date = Convert.ToDateTime(cols[0]);
EODData.OpenPrice = Convert.ToDecimal(cols[1]);
EODData.HighPrice = Convert.ToDecimal(cols[2]);
EODData.LowhPrice = Convert.ToDecimal(cols[3]);
EODData.ClosePrice = Convert.ToDecimal(cols[4]);
EODData.Volume = Convert.ToDecimal(cols[5]);
EODData.AdjPrice = Convert.ToDecimal(cols[6]);
}
i = ++i;
}
return(EODData);
}
//method fetch
public YahooEODData fetchHP(string id, DateTime StartDate, DateTime EndDate)
{
string DataString;
DataString = "http://ichart.finance.yahoo.com/table.csv?s=" + id +
"&d=" + (StartDate.Month - 1) + "&e=" + StartDate.Day + "&f=" + StartDate.Year +
"&g=d&a=" + (EndDate.Month - 1) + "&b=" + EndDate.Day + "&c=" + EndDate.Year + "&ignore=.csv";
WebClient web = new WebClient();
YahooEODData EODData = new YahooEODData();
try
{
string csvData = web.DownloadString(DataString);
EODData = Parse(csvData);
Console.WriteLine(id + " is downloaded successfully from yahoo finance");
}
catch
{
Console.WriteLine(id + " can not be found in yahoo finance");
}
decimal y = 0;
return(EODData);
}
//method
private static YahooEODData Parse(string csvData)
{
YahooEODData EODData = new YahooEODData();
string[] rows = csvData.Replace("\r", "").Split('\n');
int i = 1;
foreach (string row in rows)
{
if (string.IsNullOrEmpty(row)) continue;
string[] cols = row.Split(',');
if (i != 1)
{
EODData.Date = Convert.ToDateTime(cols[0]);
EODData.OpenPrice = Convert.ToDecimal(cols[1]);
EODData.HighPrice = Convert.ToDecimal(cols[2]);
EODData.LowhPrice = Convert.ToDecimal(cols[3]);
EODData.ClosePrice = Convert.ToDecimal(cols[4]);
EODData.Volume = Convert.ToDecimal(cols[5]);
EODData.AdjPrice = Convert.ToDecimal(cols[6]);
}
i = ++i;
}
return EODData;
}
//method fetch
public YahooEODData fetchHP(string id, DateTime StartDate, DateTime EndDate)
{
string DataString;
DataString = "http://ichart.finance.yahoo.com/table.csv?s=" + id +
"&d=" + (StartDate.Month - 1) + "&e=" + StartDate.Day + "&f=" + StartDate.Year +
"&g=d&a=" + (EndDate.Month-1) + "&b=" + EndDate.Day + "&c=" + EndDate.Year + "&ignore=.csv";
WebClient web = new WebClient();
YahooEODData EODData = new YahooEODData();
try
{
string csvData = web.DownloadString(DataString);
EODData = Parse(csvData);
Console.WriteLine(id + " is downloaded successfully from yahoo finance");
}
catch
{
Console.WriteLine(id + " can not be found in yahoo finance");
}
decimal y = 0;
return EODData;
}
public void Upload(YahooEODData YahooData)
{
//connect to server
string ConnectionString = "Data Source=HAO-PC\\SQLEXPRESS;Initial Catalog=Live;Integrated Security=True";
SqlConnection dbconnection = new SqlConnection(ConnectionString);
dbconnection.Open();
// sql command
SqlCommand cmd = new SqlCommand("INSERT INTO YahooEODPrice (Date, Ticker,OpenPrice,HighPrice, LowPrice, ClosePrice, Volume, AdjPrice)" +
"VALUES (@Date, @Ticker,@OpenPrice,@HighPrice, @LowPrice, @ClosePrice, @Volume, @AdjPrice)");
//upload data
cmd.CommandType = CommandType.Text;
cmd.Connection = dbconnection;
cmd.Parameters.AddWithValue("@Date", YahooData.Date);
cmd.Parameters.AddWithValue("@Ticker", YahooData.ID);
cmd.Parameters.AddWithValue("@OpenPrice", YahooData.OpenPrice);
cmd.Parameters.AddWithValue("@HighPrice", YahooData.HighPrice);
cmd.Parameters.AddWithValue("@LowPrice", YahooData.LowhPrice);
cmd.Parameters.AddWithValue("@ClosePrice", YahooData.ClosePrice);
cmd.Parameters.AddWithValue("@Volume", YahooData.Volume);
cmd.Parameters.AddWithValue("@AdjPrice", YahooData.AdjPrice);
cmd.ExecuteNonQuery();
cmd.Dispose();
dbconnection.Close();
}
static void Main()
{
Console.WriteLine("Start Downloading Data From Yahoo");
// testing code ****************************************
//YahooYQL x = new YahooYQL();
string BASE_URL = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22GLD%22)&env=store://datatables.org/alltableswithkeys";
//decimal z = x.fetch(BASE_URL);
// testing code ****************************************
// Production Code Start From this line
//-----------------------------------------------------------------------------------
//sql connection: fetch all yahoo ticker
SqlConnection dbconnection1 = new SqlConnection("Data Source=HAO-PC\\SQLEXPRESS;Initial Catalog=Live;Integrated Security=True");
dbconnection1.Open();
//download ticker from database
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM YahooTicker";
cmd.Connection = dbconnection1;
SqlDataReader reader;
reader = cmd.ExecuteReader();
// load sql data to datatable
DataTable dataTable = new DataTable();
dataTable.Load(reader);
//Get number of rows
//int rowCount = rowCount = dataTable.Rows.Count;
//string[] IDArr = new string[rowCount];
// set up datetime obj
DateTime startdate = new DateTime();
DateTime enddate = new DateTime();
// set it to t -1 date
startdate = DateTime.Now.AddDays(-1);
enddate = DateTime.Now.AddDays(-1);
// set up yahoo connection obj
YahooEOD YahooConnection = new YahooEOD();
YahooEODData YahooData = new YahooEODData();
string myid;
// SQL Connection
YahooEODSQLUploader Uploader = new YahooEODSQLUploader();
// main loop
for (int i = 0; i < dataTable.Rows.Count; i++)
{
// get id from sql server
myid = dataTable.Rows[i][0].ToString();
// download price from yahoo finance
YahooData = YahooConnection.fetchHP (myid, startdate, enddate);
YahooData.ID = myid;
if (YahooData.OpenPrice != 0)
{
Uploader.Upload(YahooData);
}
}
}
static void Main()
{
Console.WriteLine("Start Downloading Data From Yahoo");
// testing code ****************************************
//YahooYQL x = new YahooYQL();
string BASE_URL = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22GLD%22)&env=store://datatables.org/alltableswithkeys";
//decimal z = x.fetch(BASE_URL);
// testing code ****************************************
// Production Code Start From this line
//-----------------------------------------------------------------------------------
//sql connection: fetch all yahoo ticker
SqlConnection dbconnection1 = new SqlConnection("Data Source=HAO-PC\\SQLEXPRESS;Initial Catalog=Live;Integrated Security=True");
dbconnection1.Open();
//download ticker from database
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM YahooTicker";
cmd.Connection = dbconnection1;
SqlDataReader reader;
reader = cmd.ExecuteReader();
// load sql data to datatable
DataTable dataTable = new DataTable();
dataTable.Load(reader);
//Get number of rows
//int rowCount = rowCount = dataTable.Rows.Count;
//string[] IDArr = new string[rowCount];
// set up datetime obj
DateTime startdate = new DateTime();
DateTime enddate = new DateTime();
// set it to t -1 date
startdate = DateTime.Now.AddDays(-1);
enddate = DateTime.Now.AddDays(-1);
// set up yahoo connection obj
YahooEOD YahooConnection = new YahooEOD();
YahooEODData YahooData = new YahooEODData();
string myid;
// SQL Connection
YahooEODSQLUploader Uploader = new YahooEODSQLUploader();
// main loop
for (int i = 0; i < dataTable.Rows.Count; i++)
{
// get id from sql server
myid = dataTable.Rows[i][0].ToString();
// download price from yahoo finance
YahooData = YahooConnection.fetchHP(myid, startdate, enddate);
YahooData.ID = myid;
if (YahooData.OpenPrice != 0)
{
Uploader.Upload(YahooData);
}
}
}