Exemple #1
0
        private static string findFirstSheetName(string sourceFilePath)
        {
            string connString = _oleDbConnectionString.Replace("$FILEPATH$", sourceFilePath);

            using (var conn = new OleDbConnection(connString))
            {
                try
                {
                    conn.Open();
                    //Get the data table containg the schema guid.
                    using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                    {
                        if (dt == null)
                        {
                            return(null);
                        }
                        //GetOleDbSchemaTable returns sheets in reverse order
                        //so the first sheet from left to right appears last
                        //other objects are also returned, but only sheet names end with $
                        int i = 0;
                        //default to XLS
                        string lastSheetName = "XLS";
                        foreach (DataRow row in dt.Rows)
                        {
                            string name = row["TABLE_NAME"].ToString();
                            if (name.EndsWith("$"))
                            {
                                lastSheetName = name;
                            }
                            i++;
                        }
                        return(lastSheetName);
                    }
                }
                catch
                {
                    ErrorHelpers.deferredEx(_alreadyOpenError);
                }
                return(null);
            }
        }
Exemple #2
0
        public static DataTable convertXLSToDataTable(string sourceFilePath)
        {
            string strConn = String.Concat(_oleDbConnectionString.Replace("$FILEPATH$", sourceFilePath));

            using (var conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();

                    string sheetName = findFirstSheetName(sourceFilePath);
                    using (var cmd = new OleDbCommand(String.Concat("SELECT * FROM [", sheetName, "]"), conn))
                    {
                        //select the data from the spreadsheet and convert it to a DataTable
                        cmd.CommandType = CommandType.Text;
                        using (var da = new OleDbDataAdapter(cmd))
                        {
                            using (var dt = new DataTable())
                            {
                                try
                                {
                                    da.Fill(dt);
                                    return(dt);
                                }
                                catch (Exception)
                                {
                                    ErrorHelpers.immediateEx(String.Format("ERROR. Could not find data in a worksheet named {0}.\nPlease select a different file.", sheetName));
                                }
                            }
                        }
                    }
                }
                catch {
                    ErrorHelpers.immediateEx(_alreadyOpenError);
                }
            }
            return(null);
        }
Exemple #3
0
 public static string getConfigVal(string keyName)
 {
     if (_htConfig == null)
     {
         loadConfigFile();
     }
     if (_htConfig.ContainsKey(keyName))
     {
         //only stock symbol values are returned in uppercase
         if (keyName.StartsWith("symbol"))
         {
             return(_htConfig[keyName].ToString().ToUpper());
         }
         else
         {
             return(_htConfig[keyName].ToString().ToLower());
         }
     }
     else
     {
         ErrorHelpers.immediateEx((String.Format("ERROR. Could not find the configuration value for {0}.", keyName)));
     }
     return("");
 }
Exemple #4
0
        //returns the results that need to be displayed on the second form
        private string processDataTable(DataTable sheetData)
        {
            int index_reportDateMMDDYYYY, index_CFTCContractMarketCode, index_openInterestAll, index_nonCommPositionsLongAll, index_nonCommPositionsShortAll, index_commPositionsLongAll, index_commPositionsShortAll, index_nonReptPositionsLongAll, index_nonReptPositionsShortAll;

            index_reportDateMMDDYYYY = index_CFTCContractMarketCode = index_openInterestAll = index_nonCommPositionsLongAll = index_nonCommPositionsShortAll = index_commPositionsLongAll = index_commPositionsShortAll = index_nonReptPositionsLongAll = index_nonReptPositionsShortAll = -1;

            var metastockCommercialLines = new ArrayList();
            var metastockLargeLines      = new ArrayList();
            var metastockSmallSpecLines  = new ArrayList();
            var metastockSpread          = new ArrayList();

            //Determine the index of the important columns
            //assume the first row [0] contains the column names
            for (int i = 0; i < sheetData.Columns.Count; i++)
            {
                if (sheetData.Rows[0][i] != null)
                {
                    string colName = sheetData.Rows[0][i].ToString().Trim().ToLower();
                    if (colName.Equals(ConfigHelpers.getConfigVal("reportdate")))
                    {
                        index_reportDateMMDDYYYY = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("markecode")))
                    {
                        index_CFTCContractMarketCode = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("openinterest")))
                    {
                        index_openInterestAll = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("largetraderlongpositions")))
                    {
                        index_nonCommPositionsLongAll = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("largetradershortpositions")))
                    {
                        index_nonCommPositionsShortAll = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("commerciallongpositions")))
                    {
                        index_commPositionsLongAll = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("commercialshortpositions")))
                    {
                        index_commPositionsShortAll = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("smallspeclongpositions")))
                    {
                        index_nonReptPositionsLongAll = i;
                    }
                    else if (colName.Equals(ConfigHelpers.getConfigVal("smallspecshortpositions")))
                    {
                        index_nonReptPositionsShortAll = i;
                    }
                }
            }
            string columnNotFoundError = "     - Could not find {0} data in the supplied spreadsheet.";

            if (index_reportDateMMDDYYYY == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Report Date"));
            }
            if (index_CFTCContractMarketCode == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Market Code"));
            }
            if (index_openInterestAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Open Interest"));
            }
            if (index_nonCommPositionsLongAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Large Traders Long Positions"));
            }
            if (index_nonCommPositionsShortAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Large Traders Short Positions"));
            }
            if (index_commPositionsLongAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Commercials Long Positions"));
            }
            if (index_commPositionsShortAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Commercials Short Positions"));
            }
            if (index_nonReptPositionsLongAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Small Speculators Short Positions"));
            }
            if (index_nonReptPositionsShortAll == -1)
            {
                ErrorHelpers.deferredEx(String.Format(columnNotFoundError, "Small Speculators Short Positions"));
            }

            List <string> errLst = ErrorHelpers.errorMessages();

            if (errLst.Count > 0)
            {
                string errorsStr = String.Join(Environment.NewLine, errLst.ToArray());
                errorsStr = String.Concat("ERROR:", Environment.NewLine, errorsStr, Environment.NewLine, "Please supply a valid Futures-Only COT Traders in Financial Futures file from the CFTC website.");
                //Cancel processing.
                throw new Exception(errorsStr);
            }

            var sbResults = new StringBuilder("", 10000);

            sbResults.AppendFormat("#\t\tDATE\t\t\tCOM\t\tLG\t\tSM\t\tSPR\n");
            int count     = 0;
            int totalRows = sheetData.Rows.Count;

            //i=1 to skip the first line
            for (int i = 1; i < totalRows; i++)
            {
                //must have at least the following columns:
                //date,open,high,low,close,volume
                DataRow cells       = sheetData.Rows[i];
                string  strDate     = Helpers.getStrValue(cells[index_reportDateMMDDYYYY]);
                string  market_Code = Helpers.getStrValue(cells[index_CFTCContractMarketCode]);
                if (market_Code.Equals(ConfigHelpers.getConfigVal("spmarketcode")))
                {
                    string metastockDate = strDate.formatStrDateToMetastockDate();
                    if (metastockDate != "")
                    {
                        count++;
                        double openInterest     = Helpers.getDblValue(cells[index_openInterestAll]);
                        double largeLong        = Helpers.getDblValue(cells[index_nonCommPositionsLongAll]);
                        double largeShort       = Helpers.getDblValue(cells[index_nonCommPositionsShortAll]);
                        double commercialsLong  = Helpers.getDblValue(cells[index_commPositionsLongAll]);
                        double commercialsShort = Helpers.getDblValue(cells[index_commPositionsShortAll]);
                        double smallSpecsLong   = Helpers.getDblValue(cells[index_nonReptPositionsLongAll]);
                        double smallSpecsShort  = Helpers.getDblValue(cells[index_nonReptPositionsShortAll]);
                        double netCommercials   = 0;
                        double netLargeTraders  = 0;
                        double netSmallSpec     = 0;
                        double netSpread        = 0;
                        //ensure division by zero will never happen by accident
                        if (openInterest > 0)
                        {
                            netCommercials  = Math.Round(((commercialsLong - commercialsShort) / openInterest) * 100, 2);
                            netLargeTraders = Math.Round(((largeLong - largeShort) / openInterest) * 100, 2);
                            netSmallSpec    = Math.Round(((smallSpecsLong - smallSpecsShort) / openInterest) * 100, 2);
                            netSpread       = Math.Round((netCommercials - netSmallSpec), 2);
                        }
                        else
                        {
                            //Cancel processing.
                            throw new Exception(String.Format("ERROR. Cannot convert line {0}. Open Interest is zero.", i.ToString()));
                        }
                        sbResults.AppendFormat("{0}\t\t{1}\t\t{2}\t\t{3}\t\t{4}\t\t{5}\n", count.ToString(), metastockDate, netCommercials, netLargeTraders, netSmallSpec, netSpread);

                        metastockCommercialLines.Add(String.Format("SP500 Fut COT COM,{0},D,{1},000000,{2},{2},{2},{2},0", ConfigHelpers.getConfigVal("symbolcotcommercials"), metastockDate, netCommercials));
                        metastockLargeLines.Add(String.Format("SP500 Fut COT LG,{0},D,{1},000000,{2},{2},{2},{2},0", ConfigHelpers.getConfigVal("symbolcotlargetraders"), metastockDate, netLargeTraders));
                        metastockSmallSpecLines.Add(String.Format("SP500 Fut COT SM,{0},D,{1},000000,{2},{2},{2},{2},0", ConfigHelpers.getConfigVal("symbolcotsmallspeculators"), metastockDate, netSmallSpec));
                        metastockSpread.Add(String.Format("SP500 Fut COT Spread,{0},D,{1},000000,{2},{2},{2},{2},0", ConfigHelpers.getConfigVal("symbolcotspread"), metastockDate, netSpread));
                    }
                    else
                    {
                        //Cancel processing.
                        throw new Exception(String.Format("ERROR. Cannot convert line {0}. Missing date.\n", i.ToString()));
                    }
                }
            }
            metastockCommercialLines.Sort();
            metastockLargeLines.Sort();
            metastockSmallSpecLines.Sort();
            metastockSpread.Sort();

            string comFilePath    = Path.Combine(_destFolder, String.Format("{0}_W.txt", ConfigHelpers.getConfigVal("symbolcotcommercials")));
            string lgFilePath     = Path.Combine(_destFolder, String.Format("{0}_W.txt", ConfigHelpers.getConfigVal("symbolcotlargetraders")));
            string smFilePath     = Path.Combine(_destFolder, String.Format("{0}_W.txt", ConfigHelpers.getConfigVal("symbolcotsmallspeculators")));
            string spreadFilePath = Path.Combine(_destFolder, String.Format("{0}_W.txt", ConfigHelpers.getConfigVal("symbolcotspread")));

            //save ArrayLists to files
            Helpers.outputArrayListToFile(comFilePath, metastockCommercialLines);
            Helpers.outputArrayListToFile(lgFilePath, metastockLargeLines);
            Helpers.outputArrayListToFile(smFilePath, metastockSmallSpecLines);
            Helpers.outputArrayListToFile(spreadFilePath, metastockSpread);

            sbResults.AppendFormat("\nConversion was successful. {0} lines processed. {1} lines extracted.", totalRows, metastockCommercialLines.Count.ToString());

            //return the process' summary as a string
            return(sbResults.ToString());
        }