public bool GetRisk(ref List <tbloptioninputdata> riskFreeInterestRates)
{
AsyncTaskListener.LogMessage("Reading Risk Free Interest Rate from TMLDB...");
try
{
var idoptioninputsymbol = Context.tbloptioninputsymbols.Where(item2 =>
item2.idoptioninputtype == 1).First().idoptioninputsymbol;
tbloptioninputdata[] tbloptioninputdatas = Context.tbloptioninputdatas.Where(item =>
item.idoptioninputsymbol == idoptioninputsymbol).ToArray();
DateTime optioninputdatetime = new DateTime();
for (int i = 0; i < tbloptioninputdatas.Length; i++)
{
if (i != 0)
{
if (optioninputdatetime < tbloptioninputdatas[i].optioninputdatetime)
{
optioninputdatetime = tbloptioninputdatas[i].optioninputdatetime;
}
}
else
{
optioninputdatetime = tbloptioninputdatas[i].optioninputdatetime;
}
}
//--?-- From where this varable in query
var OPTION_INPUT_TYPE_RISK_FREE_RATE = 1;
//--?-- What difference between idoptioninputsymbol and idoptioninputsymbol2
var idoptioninputsymbol2 = Context.tbloptioninputsymbols.Where(item2 =>
item2.idoptioninputtype == OPTION_INPUT_TYPE_RISK_FREE_RATE).First().idoptioninputsymbol;
riskFreeInterestRates = Context.tbloptioninputdatas.Where(item =>
item.idoptioninputsymbol == idoptioninputsymbol2).ToList();
AsyncTaskListener.LogMessageFormat(
"Count of RiskFreeInterestRates items = {0}",
riskFreeInterestRates.Count);
return(true);
}
catch (InvalidOperationException)
{
return(false);
}
catch (SqlException)
{
return(false);
}
}
void DropTempTables()
{
string exception2 = "Cannot drop the table 'tempOptionData', because it does not exist or you do not have permission.";
string exception3 = "Cannot drop the table 'temp', because it does not exist or you do not have permission.";
string exception1 = exception3 + "\r\n" + exception2;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
string query = "DROP TABLE temp; DROP TABLE tempOptionData;";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (ex.Message == exception1)
{
AsyncTaskListener.LogMessage("No tables to drop");
}
else if (ex.Message == exception2)
{
AsyncTaskListener.LogMessage("Dropped 'temp' table");
}
else if (ex.Message == exception3)
{
AsyncTaskListener.LogMessage("Dropped 'tempOptionData' table");
}
else
{
AsyncTaskListener.LogMessage(ex.Message);
}
}
}
}
}
async Task PushDataToDB(CancellationToken ct)
{
progressBar.Maximum = ParsedData.FutureRecords.Count;
if (!ParsedData.FuturesOnly)
{
progressBar.Maximum += ParsedData.OptionRecords.Count;
}
int globalCount = 0;
DateTime start = DateTime.Now;
AsyncTaskListener.Init();
try
{
AsyncTaskListener.Init("Pushing of FUTURES data started");
await Task.Run(() => PushContractsTable(ref globalCount, ct), ct);
LogElapsedTime(DateTime.Now - start);
AsyncTaskListener.LogMessage("Pushing of FUTURES data complete");
if (!ParsedData.FuturesOnly)
{
AsyncTaskListener.LogMessage("Pushing of OPTIONS data started");
await Task.Run(() => PushOptionsTable(ref globalCount, ct), ct);
LogElapsedTime(DateTime.Now - start);
AsyncTaskListener.LogMessage("Pushing of OPTIONS data complete");
}
}
catch (OperationCanceledException)
{
// Already logged
}
finally
{
EnableDisable(false);
}
LogMessage(string.Format("Pushed to DB: {0} entries", globalCount));
}
void PushOneTable(DataTable table, string tableType, string spNameRoot, SqlConnection connection)
{
string spNamePrefix = cb_TestTables.Checked ? "[cqgdb].test_" : "[cqgdb].";
string spName = spNamePrefix + spNameRoot;
using (SqlCommand cmd = new SqlCommand(spName, connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddWithValue("@" + tableType, table);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
AsyncTaskListener.LogMessage(ex.Message);
}
}
}
public bool GetThreeParams(string description, ref long?idInstrument, ref string cqgSymbol, ref double?tickSize)
{
AsyncTaskListener.LogMessage("Reading ID Instrument, CQG Symbol and Tick Size from TMLDB...");
tblinstrument record;
try
{
record = Context.tblinstruments.Where(item => item.description == description).First();
}
catch (InvalidOperationException)
{
return(false);
}
catch (SqlException e)
{
MessageBox.Show(
e.Message,
"ICE Import (DB Form)",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
return(false);
}
idInstrument = record.idinstrument;
cqgSymbol = record.cqgsymbol;
double secondaryoptionticksize = record.secondaryoptionticksize;
tickSize = (secondaryoptionticksize > 0) ? secondaryoptionticksize : record.optionticksize;
AsyncTaskListener.LogMessageFormat(
"ID Instrument = {0}\nCQG Symbol = {1}\nTick Size = {2}",
idInstrument,
cqgSymbol,
tickSize);
return(true);
}
private void ValidationLogHelper <T>(HashSet <T> hash, string symbTypePlural, string tblName)
{
string logMessage = string.Empty;
if (hash.Count == 0)
{
logMessage = string.Format("All {0} were pushed to {1} successfully", symbTypePlural, tblName);
AsyncTaskListener.LogMessage(logMessage);
}
else
{
logMessage = string.Format("Failed to push {0} {1} to {2}:", hash.Count, symbTypePlural, tblName);
AsyncTaskListener.LogMessage(logMessage);
AsyncTaskListener.LogMessage("----------------------------------");
foreach (dynamic item in hash)
{
LogInvalidItem(item);
AsyncTaskListener.LogMessage("----------------------------------");
}
}
ValidationResult += logMessage + "\n";
}
private void ParsedData_ParseSucceeded()
{
string pat = "{0} entries count: {1} (ready for pushing to DB)";
string msg = string.Format(
pat,
"EOD_Futures",
ParsedData.FutureRecords.Count);
LogMessage(msg);
if (!ParsedData.FuturesOnly)
{
msg = string.Format(
pat,
"EOD_Options",
ParsedData.OptionRecords.Count);
LogMessage(msg);
// Choose StrikePriceToCQGSymbolFactor value
ParsedData.StrikePriceToCQGSymbolFactor = GetStrikePriceToCQGSymbolFactor(
ParsedData.OptionRecords.Select(item => item.StrikePrice));
AsyncTaskListener.LogMessageFormat("Chosen StrikePriceToCQGSymbolFactor value: {0}", ParsedData.StrikePriceToCQGSymbolFactor);
}
buttonPush.Enabled = true;
}
void PushContractsTable(ref int globalCount, CancellationToken ct)
{
StripNameHashSet = new HashSet <DateTime>();
StripNameDateHashSet = new HashSet <Tuple <DateTime, DateTime> >();
// Create tables
var tblContract = new DataTable();
tblContract.Columns.Add("contractname", typeof(string));
tblContract.Columns.Add("month", typeof(char));
tblContract.Columns.Add("monthint", typeof(int));
tblContract.Columns.Add("year", typeof(int));
tblContract.Columns.Add("idinstrument", typeof(int));
tblContract.Columns.Add("expirationdate", typeof(DateTime));
tblContract.Columns.Add("cqgsymbol", typeof(string));
var tblDailyContract = new DataTable();
tblDailyContract.Columns.Add("idinstrument", typeof(int));
tblDailyContract.Columns.Add("month", typeof(char));
tblDailyContract.Columns.Add("year", typeof(int));
tblDailyContract.Columns.Add("date", typeof(DateTime));
tblDailyContract.Columns.Add("price", typeof(double));
tblDailyContract.Columns.Add("volume", typeof(double));
tblDailyContract.Columns.Add("openinterest", typeof(double));
foreach (EOD_Future future in ParsedData.FutureRecords)
{
if (ct.IsCancellationRequested)
{
break;
}
bool newFuture = !StripNameHashSet.Contains(future.StripName);
char monthChar = Utilities.MonthToMonthCode(future.StripName.Month);
string contractname = Utilities.GenerateCQGSymbolFromSpan(
'F',
CqgSymbol,
monthChar,
future.StripName.Year);
string log = string.Empty;
if (newFuture)
{
DateTime expirationDate = TMLDBReader.GetExpirationDate(
"future",
(long)IdInstrument,
future.StripName,
ref log);
tblContract.Rows.Add(
contractname,
monthChar,
future.StripName.Month,
future.StripName.Year,
(int)IdInstrument,
expirationDate,
contractname);
StripNameHashSet.Add(future.StripName);
}
tblDailyContract.Rows.Add(
IdInstrument,
monthChar,
future.StripName.Year,
future.Date,
future.SettlementPrice.GetValueOrDefault(),
(long)future.Volume.GetValueOrDefault(),
(long)future.OpenInterest.GetValueOrDefault());
StripNameDateHashSet.Add(Tuple.Create(future.StripName, future.Date));
globalCount++;
AsyncTaskListener.Update(globalCount, log);
log = string.Empty;
}
#region Pushing commands
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
PushOneTable(tblContract, "contract", "SPFTable", connection);
PushOneTable(tblDailyContract, "dailycontract", "SPDFTable", connection);
}
#endregion
AsyncTaskListener.LogMessageFormat("Pushed {0} entries to {1} {2}TBLCONTRACT table", globalCount, DatabaseName, TablesPrefix);
}
void PushOptionsTable(ref int globalCount, CancellationToken ct)
{
bool newOption;
OptionNameHashSet = new HashSet <string>();
OptionDataList = new List <Tuple <string, DateTime, double> >();
// Create tables
var tblOptions = new DataTable();
tblOptions.Columns.Add("monthforfuture", typeof(int));
tblOptions.Columns.Add("yearforfuture", typeof(int));
tblOptions.Columns.Add("optionname", typeof(string));
tblOptions.Columns.Add("optionmonth", typeof(char));
tblOptions.Columns.Add("optionmonthint", typeof(int));
tblOptions.Columns.Add("optionyear", typeof(int));
tblOptions.Columns.Add("strikeprice", typeof(decimal));
tblOptions.Columns.Add("callorput", typeof(char));
tblOptions.Columns.Add("idinstrument", typeof(int));
tblOptions.Columns.Add("expirationdate", typeof(DateTime));
tblOptions.Columns.Add("cqgsymbol", typeof(string));
var tblOptionDatas = new DataTable();
tblOptionDatas.Columns.Add("optionname", typeof(string));
tblOptionDatas.Columns.Add("datetime", typeof(DateTime));
tblOptionDatas.Columns.Add("price", typeof(double));
tblOptionDatas.Columns.Add("impliedvol", typeof(double));
tblOptionDatas.Columns.Add("timetoexpinyears", typeof(double));
string log = string.Empty;
foreach (var option in ParsedData.OptionsRecordsSelected)
{
if (ct.IsCancellationRequested)
{
break;
}
try
{
char monthChar = Utilities.MonthToMonthCode(option.Option.StripName.Month);
string optionName = Utilities.GenerateOptionCQGSymbolFromSpan(
option.Option.OptionType,
CqgSymbol,
monthChar,
option.Option.StripName.Year,
(double)option.Option.StrikePrice.GetValueOrDefault() * (double)ParsedData.StrikePriceToCQGSymbolFactor);
newOption = !OptionNameHashSet.Contains(optionName);
if (newOption)
{
DateTime expirationDate = TMLDBReader.GetExpirationDate(
"option",
(long)IdInstrument,
option.Option.StripName,
ref log);
tblOptions.Rows.Add(
option.FutureStripName.Month,
option.FutureStripName.Year,
optionName,
monthChar,
option.Option.StripName.Month,
option.Option.StripName.Year,
option.Option.StrikePrice.GetValueOrDefault(),
option.Option.OptionType,
IdInstrument,
expirationDate,
optionName);
OptionNameHashSet.Add(optionName);
}
#region Implied Volatility
// callPutFlag - tableOption.callorput
// S - stock price - 1.56
// X - strike price of option - option.StrikePrice
// T - time to expiration in years - 0.5
// r - risk-free interest rate - from table tbloptioninputdata
// currentOptionPrice - option.SettlementPrice
// tickSize - from table tblinstruments (secondaryoptionticksize or optionticksize)
double riskFreeInterestRate = double.NaN;
try
{
riskFreeInterestRate = RiskFreeInterestRates.Find(item => item.optioninputdatetime == option.Option.Date).optioninputclose;
if (oldRFI != double.NaN)
{
oldRFI = riskFreeInterestRate;
}
else
{
oldRFI = 0;
}
}
catch (Exception)
{
if (!useOldRFI)
{
useOldRFI = true;
}
else
{
riskFreeInterestRate = oldRFI;
}
}
if (riskFreeInterestRate != double.NaN)
{
double impliedvol = OptionCalcs.CalculateOptionVolatilityNR(
option.Option.OptionType,
1.56,
Utilities.NormalizePrice(option.Option.StrikePrice),
0.5,
riskFreeInterestRate,
Utilities.NormalizePrice(option.Option.SettlementPrice),
(double)TickSize);
if (object.ReferenceEquals(impliedvol, null) || double.IsNaN(impliedvol) || double.IsInfinity(impliedvol))
{
impliedvol = 0;
}
#endregion
double futureYear = option.Option.StripName.Year + option.Option.StripName.Month / 12.0;
double expirateYear = option.Option.Date.Year + option.Option.Date.Month / 12.0;
tblOptionDatas.Rows.Add(
optionName,
option.Option.Date,
option.Option.SettlementPrice.GetValueOrDefault(),
impliedvol,
futureYear - expirateYear);
OptionDataList.Add(Tuple.Create(optionName, option.Option.Date, option.Option.SettlementPrice.GetValueOrDefault()));
}
else
{
int erc = globalCount - ParsedData.FutureRecords.Count - ParsedData.FutureRecords.Count;
AsyncTaskListener.LogMessageFormat("Can't find riskFreeInterestRate for item #{0}", erc);
}
}
catch (Exception ex)
{
int erc = globalCount - ParsedData.FutureRecords.Count - ParsedData.FutureRecords.Count;
log += string.Format(
"ERROR message from {0} pushing {1}TBLOPTIONS and {1}TBLOPTIONDATAS tables\n" +
"Can't push entry N: {2}\n",
DatabaseName, TablesPrefix, erc);
log += ex.Message + "\n";
continue;
}
finally
{
globalCount++;
AsyncTaskListener.Update(globalCount, log);
log = string.Empty;
}
}
#region Pushing commands
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
PushOneTable(tblOptions, "option", "SPOTable", connection);
PushOneTable(tblOptionDatas, "optiondata", "SPODTable", connection);
}
#endregion
AsyncTaskListener.LogMessageFormat("Pushed {0} entries to {1} {2}TBLOPTIONS and {2}TBLOPTIONDATAS tables", globalCount, DatabaseName, TablesPrefix);
}
async void PullDataFromDBTest()
{
cts = new CancellationTokenSource();
Dictionary <DateTime, long> idcontractDictionary = null;
Dictionary <string, long> idoptionDictionary = null;
List <test_tblcontract> contractList = null;
List <test_tbldailycontractsettlement> dailyContractList = null;
List <test_tbloption> optionList = null;
List <test_tbloptiondata> optionDataList = null;
try
{
AsyncTaskListener.LogMessage("Started pulling FUTURES data...");
await Task.Run(() => PullFuturesTest(out idcontractDictionary, out contractList), cts.Token);
AsyncTaskListener.LogMessageFormat("Pulled {0} entries from {1} {2}TBLCONTRACT table", contractList.Count, DatabaseName, TablesPrefix);
AsyncTaskListener.LogMessage("Started pulling DAILY FUTURES data...");
await Task.Run(() => PullDailyFuturesTest(idcontractDictionary, out dailyContractList), cts.Token);
AsyncTaskListener.LogMessageFormat("Pulled {0} entries from {1} {2}TBLDAILYCONTRACTSETTLEMENT table", dailyContractList.Count, DatabaseName, TablesPrefix);
AsyncTaskListener.LogMessage("Started pulling OPTIONS data...");
await Task.Run(() => PullOptionsTest(out idoptionDictionary, out optionList), cts.Token);
AsyncTaskListener.LogMessageFormat("Pulled {0} entries from {1} {2}TBLOPTIONS table", optionList.Count, DatabaseName, TablesPrefix);
AsyncTaskListener.LogMessage("Started pulling DAILY OPTIONS data...");
await Task.Run(() => PullDailyOptions(idoptionDictionary, out optionDataList), cts.Token);
AsyncTaskListener.LogMessageFormat("Pulled {0} entries from {1} {2}TBLOPTIONDATAS table", optionDataList.Count, DatabaseName, TablesPrefix);
}
catch (OperationCanceledException cancel)
{
AsyncTaskListener.LogMessage(cancel.Message);
}
#if !DEBUG
catch (Exception ex)
{
AsyncTaskListener.LogMessage("ERROR");
AsyncTaskListener.LogMessage(ex.Message);
}
#endif
finally
{
int totalCount =
contractList.Count() +
dailyContractList.Count() +
optionList.Count() +
optionDataList.Count();
AsyncTaskListener.LogMessageFormat("Pulled: {0} entries from {1} DB", totalCount, DatabaseName);
}
dataGridViewContract.DataSource = contractList;
dataGridViewDailyContract.DataSource = dailyContractList;
dataGridViewOption.DataSource = optionList;
dataGridViewOptionData.DataSource = optionDataList;
EnableDisable(false);
}
/// <summary>
/// Install stored procedures from SQL files into DB
/// </summary>
public static bool Install(
string connectionString,
bool isTestTables,
CancellationToken ct)
{
int spBunchIdx = isTestTables ? 1 : 0;
if (isSPInstalled[spBunchIdx])
{
AsyncTaskListener.LogMessage("Stored procedures were installed before");
return(true);
}
AsyncTaskListener.LogMessage("Started installing stored procedures...");
// Get paths of all files in "StoredProcs" directory
string baseDir = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
string subDir = Path.Combine(baseDir, storedProcsDir);
string[] filePaths = Directory.GetFiles(subDir);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
foreach (string filePath in filePaths)
{
if (ct.IsCancellationRequested)
{
connection.Close();
break;
}
// Do not install "test_" stored procedures if we are working with non-test tables and vice versa
string fileName = Path.GetFileName(filePath);
bool isTestStoredProc = fileName.Contains(testTablesPrefix);
bool skip = isTestStoredProc ^ isTestTables;
if (skip)
{
AsyncTaskListener.LogMessage(" " + fileName + " - skipped");
continue;
}
// Install the new stored procedure from SQL file into DB
string storedProcBody = File.ReadAllText(filePath);
var createProcCommand = new SqlCommand(storedProcBody, connection);
try
{
createProcCommand.ExecuteNonQuery();
isSPInstalled[spBunchIdx] = true;
}
catch (SqlException ex)
{
AsyncTaskListener.LogMessage(ex.Message);
// Remove the old stored procedure from DB
string procName = fileName.Substring(0, fileName.Length - storedProcFileExt.Length);
string dropProcCommandBody = string.Format(dropProcCommandPattern, procName);
using (var dropProcCommand = new SqlCommand(dropProcCommandBody, connection))
{
try
{
dropProcCommand.ExecuteNonQuery();
}
catch (SqlException exc)
{
AsyncTaskListener.LogMessage(exc.Message);
AsyncTaskListener.LogMessage(" " + fileName + " - FAILED");
isSPInstalled[spBunchIdx] = false;
return(false);
}
}
// Try again
Thread.Sleep(2000);
try
{
createProcCommand.ExecuteNonQuery();
}
catch (SqlException)
{
}
}
AsyncTaskListener.LogMessage(" " + fileName + " - done");
}
connection.Close();
}
AsyncTaskListener.LogMessage("Completed installing stored procedures");
return(true);
}
private void LogInvalidItem(Tuple <DateTime, DateTime, double> tuple)
{
AsyncTaskListener.LogMessage(" - StripName " + tuple.Item1);
AsyncTaskListener.LogMessage(" - Date " + tuple.Item2);
AsyncTaskListener.LogMessage(" - Expirationdate " + tuple.Item3);
}
private void LogInvalidItem(DateTime dt)
{
AsyncTaskListener.LogMessage(" - StripName " + dt);
}
