public FormDB()
{
InitializeComponent();
this.Resize += FormDB_Resize;
this.LogMessage += FormDB_LogMessage;
this.FormClosed += FormDB_FormClosed;
ParsedData.ParseSucceeded += ParsedData_ParseSucceeded;
ParsedData.ParseFailed += ParsedData_ParseFailed;
AsyncTaskListener.Updated += AsyncTaskListener_Updated;
rb_DB_CheckedChanged(rb_LocalDB, null);
cb_TestTables_CheckedChanged(null, null);
var contextTMLDB = new DataClassesTMLDBDataContext(ConnectionStrings.TMLDB);
TMLDBReader = new TMLDBReader(contextTMLDB);
}
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);
}