예제 #1
0
        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);
            }
        }
예제 #2
0
        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);
                        }
                    }
                }
            }
        }
예제 #3
0
        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));
        }
예제 #4
0
        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);
                }
            }
        }
예제 #5
0
        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";
        }
예제 #7
0
        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;
        }
예제 #8
0
        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);
        }
예제 #9
0
        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);
        }
예제 #10
0
        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);
 }