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);
}
}
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);
}
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("");
}
//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());
}
