public static void RedoByYear()
{
int counter = 1;
int routeId = 0;
int numUpdated = 0;
DateTime cutoffDate = new DateTime(2015, 1, 1);
DateTime startDate = new DateTime(2017, 01, 01);
DateTime endDate = new DateTime(2017, 12, 31);
DateTime startTime = DateTime.Now;
DateTime currentTime;
TimeSpan elapsedTime;
string duration = TradingTime.GetNumTradingDays(startDate, endDate).ToString() + " D";
int numberSecurities = IB.masterSecurityList.Contracts.Count();
Log.Info(3, string.Format("Start RedoByYear"));
foreach (Security tempSecurity in IB.masterSecurityList.Contracts.ToList())
{
#region Calculate Time Remaining
currentTime = DateTime.Now;
elapsedTime = currentTime - startTime;
string elapsed = string.Format("E={0:D2}:{1:D2}:{2:D2}", elapsedTime.Hours, elapsedTime.Minutes, elapsedTime.Seconds);
TimeSpan timeLeft = (numberSecurities - counter) * (elapsedTime / counter);
string Remaining = string.Format("Remaining={0:D2}:{1:D2}:{2:D2}", timeLeft.Hours, timeLeft.Minutes, timeLeft.Seconds);
double percentCompleted = counter / numberSecurities;
Log.Data(1, string.Format("{0,-10} {1,-10} ActiveReq={2,2} OpenReq={2,2} " + elapsed + " " + Remaining, tempSecurity.Symbol, tempSecurity.ConId, Program.myIB.myRequests.NumActiveRequests, Program.myIB.myRequests.NumOpenRequests));
counter++;
#endregion Calculate Time Remaining
BarDataList myBDL = new BarDataList();
myBDL = DataRequests.GetAllBars(tempSecurity.Symbol);
int numBadEditDates = myBDL.getNumBadEditDates(startDate, endDate, cutoffDate);
if (numBadEditDates > 14)
{
numUpdated++;
Program.myIB.RequestBarIB(routeId, tempSecurity.ConId, tempSecurity.Symbol, endDate.ToString("yyyyMMdd hh:MM:ss"), duration);
Log.Info(3, string.Format("RedoByYear Requesting Symbol= {0,-10} Bad Edits={1,-5}", tempSecurity.Symbol, numBadEditDates));
Thread.Sleep(Program.myIB.myRequests.DelayTime);
}
else
{
Log.Info(3, string.Format("RedoByYear Skipping Symbol={0,-10} Bad Edits={1,-5}", tempSecurity.Symbol, numBadEditDates));
}
}
Log.Info(3, string.Format("End RedoByYear - Updated={0} Total={1}", numUpdated, numberSecurities));
}
public static BarDataList GetAllBars(string symbol)
{
Log.Data(2, string.Format("Enter GetAllBars {0,-12}", symbol));
BarDataList mBarDataList = new BarDataList();
// SELECT ticker, tdate, volume, open, high, low, close, editdate FROM historical WHERE ticker = "FB";
string cmdString = "SELECT ticker, tdate, volume, open, high, low, close, editdate FROM " + DB.myHistTable + " WHERE ticker='" + symbol + "'";
mBarDataList = DB.ReadAllBars(cmdString);
mBarDataList.ConId = GetConId(symbol);
return(mBarDataList);
}
public static void RedoByEditdate()
{
int counter = 1;
int numUpdated = 0;
int routeId = 0;
DateTime startTime = DateTime.Now;
DateTime currentTime;
DateTime cutoffDate;
TimeSpan elapsedTime;
//foreach (Security tempSecurity in IB.masterSecurityList.Contracts.AsEnumerable().Reverse().ToList())
Log.Info(3, string.Format("Start RedoByEditdate"));
int numberSecurities = IB.masterSecurityList.Contracts.Count();
foreach (Security tempSecurity in IB.masterSecurityList.Contracts.ToList())
{
#region Calculate Time Remaining
currentTime = DateTime.Now;
elapsedTime = currentTime - startTime;
string Elapsed = string.Format("Elapsed={0:D2}:{1:D2}:{2:D2}", elapsedTime.Hours, elapsedTime.Minutes, elapsedTime.Seconds);
TimeSpan timeLeft = (numberSecurities - counter) * (elapsedTime / counter);
string Remaining = string.Format("Remaining={0:D2}:{1:D2}:{2:D2}:{3:D2}", timeLeft.Days, timeLeft.Hours, timeLeft.Minutes, timeLeft.Seconds);
double percentCompleted = counter / numberSecurities;
Log.Data(1, string.Format("Symbol={0,-10} ConId={1,-10} requests={2,2} " + Elapsed + " " + Remaining, tempSecurity.Symbol, tempSecurity.ConId, Program.myIB.myRequests.NumOpenRequests));
counter++;
#endregion Calculate Time Remaining
BarDataList myBDL = new BarDataList();
myBDL = DataRequests.GetAllBars(tempSecurity.Symbol);
foreach (BarDataItem tempBar in myBDL.Items)
{
cutoffDate = tempBar.Time.AddDays(7);
if (tempBar.EditTime < cutoffDate && DateTime.Now > cutoffDate)
{
Program.myIB.RequestBarIB(routeId, myBDL.ConId, tempBar.Time);
numUpdated++;
Thread.Sleep(Program.myIB.myRequests.DelayTime);
}
}
}
Log.Info(3, string.Format("End RedoByEditdate - {0} Updated", numUpdated));
}
/// <summary>
/// Callback function that writes the historical data into the database
/// </summary>
/// <param name="reqId"></param>
/// <param name="bar"></param>
/// <param name="end"></param>
public static void WriteBarListDB(int reqId, int conId, string symbol, BarDataList barDataList, bool end)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
foreach (BarDataItem barItem in barDataList.Items)
{
WriteBarDB(conId, barItem, symbol, false);
}
stopWatch.Stop();
Log.Info(2, string.Format("End WriteBarListDB {0,-8} {1,-10} Records Writen={2}, Time= {3:F0} Average= {4:F0}", symbol, conId, barDataList.Items.Count, stopWatch.Elapsed.TotalMilliseconds, stopWatch.Elapsed.TotalMilliseconds / barDataList.Items.Count));
Program.myIB.myRequests.Delete(reqId);
Program.myIB.myBDL.Clear();
}
public static BarDataList ReadAllBars(string cmdString)
{
BarDataList mBarDataList = new BarDataList();
MySqlConnection connection = new MySqlConnection(myConnPath);
connection.Open();
MySqlCommand cmd = new MySqlCommand(cmdString, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
int numBars = 0;
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// SELECT ticker, tdate, volume, open, high, low, close, editdate FROM historical WHERE ticker = "FB";
while (dataReader.Read())
{
BarDataItem mBarData = new BarDataItem(
dataReader.GetString(1), // Time
dataReader.GetDouble(3), // Open
dataReader.GetDouble(4), // High
dataReader.GetDouble(5), // Low
dataReader.GetDouble(6), // Close
dataReader.GetUInt32(2), // Volume
0, // Other
0); // Other
mBarData.editTime = dataReader.GetDateTime(7); // EditTime
mBarDataList.Add(mBarData);
numBars++;
Log.Data(0, string.Format("{0,20} {1,8} {2:0.000} {3:0.000} {4:0.000} {5:0.000} {6:MM/dd/yyyy HH:mm:ss}",
mBarData.Time, mBarData.Volume, mBarData.Open, mBarData.High, mBarData.Low, mBarData.Close, mBarData.editTime));
}
stopwatch.Stop();
if (numBars != 0)
{
Log.Info(2, string.Format("End ReadAllBars Changed={0} Time/Rec= {1:F}", numBars, stopwatch.Elapsed.TotalMilliseconds / numBars));
}
dataReader.Close();
connection.Close();
return(mBarDataList);
}
/// <summary>
/// NOT COMPLETED
/// </summary>
/// <param name="command">string representing the command to be executed</param>
public static int WriteBarDataList(BarDataList writeBDL)
{
MySqlConnection connection = new MySqlConnection(myConnPath);
connection.Open();
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
int numBars = 0;
foreach (var bar in writeBDL.Items)
{
string symbol = writeBDL.Symbol;
int conId = writeBDL.ConId;
double change = 0.0;
string editDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string cmdString = "REPLACE INTO " + DB.myHistTable + " VALUES ('" + symbol + "','" + conId + "','" + bar.Time + "','" +
bar.Volume + "','" + bar.Open + "','" + bar.High + "','" + bar.Low + "','" + bar.Close + "','" + change + "','" + editDate + "')";
MySqlCommand cmd = new MySqlCommand(cmdString, connection);
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
Log.Data(3, string.Format("Insert {0,-8} {1,-10} {2,-6} {3,-8} {4,-6} {5,-6} {6,-6} {7,-6}", symbol, conId, bar.Time.ToString("dd/MM/yyyy"), bar.Volume, bar.Open, bar.High, bar.Low, bar.Close, change));
}
else
{
Log.Data(3, string.Format("Replace {0,-8} {1,-10} {2,-6} {3,-8} {4,-6} {5,-6} {6,-6} {7,-6}", symbol, conId, bar.Time.ToString("dd/MM/yyyy"), bar.Volume, bar.Open, bar.High, bar.Low, bar.Close, change));
}
numBars++;
}
stopwatch.Stop();
Log.Info(2, string.Format("End WriteBarDataList Changed={0} Time/Rec= {1}", numBars, stopwatch.Elapsed.TotalMilliseconds / numBars));
connection.Close();
return(numBars);
}
private static void SaveData(string symbol)
{
string dataFileName = symbol + ".txt";
// Set a variable to the Documents path.
string myDocumentsPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
// Set a variable to the Stocks path.
myDocumentsPath = "D:\\OneDrive\\Stocks\\Deleted\\";
// Use full path to create a handle to the error file
System.IO.StreamWriter dataFile = new StreamWriter(Path.Combine(myDocumentsPath, dataFileName), false);
BarDataList mBarDataList = new BarDataList();
mBarDataList = DataRequests.GetAllBars(symbol);
foreach (BarDataItem mItem in mBarDataList.Items)
{
dataFile.WriteLine(string.Format("{0:MM/dd/yyyy} {1:0.000} {2:0.000} {3:0.000} {4:0.000} {5,-8} {6:yyyyMMdd HH:mm:ss}",
mItem.Time, mItem.Open, mItem.High, mItem.Low, mItem.Close, mItem.Volume, mItem.EditTime));
}
dataFile.Close();
}
