public static void outputArrayListToFile(string path, ArrayList al)
{
//open streamwriter with overwrite option
using (var writer = new StreamWriter(path, false))
{
//add Metastock headers from configuration file
writer.WriteLine(ConfigHelpers.getConfigVal("metastockheaders").ToUpper());
foreach (Object obj in al)
{
if (obj != null)
{
string line = obj.ToString();
if (line != String.Empty)
{
writer.WriteLine(line);
}
}
}
}
}
private void btnBrowse_Click(object sender, RoutedEventArgs e)
{
var fileBrowser = new OpenFileDialog();
//default file extension
fileBrowser.DefaultExt = ".xls";
// file extension filter
fileBrowser.Filter = "Excel Files (*.xls)|*.xls";
Nullable <bool> selectedFile = fileBrowser.ShowDialog();
if (selectedFile == true)
{
//Excel file path
_sourceFilePath = fileBrowser.FileName;
txtFilePath.Text = _sourceFilePath;
//destination folder is the same folder as the source Excel file
_destFolder = Path.GetDirectoryName(_sourceFilePath).ToString();
//show the destination paths
lblOutputPath.Content = String.Format("{0}\n{1}\n{2}\n{3}",
Helpers.getPath(_destFolder, String.Concat(ConfigHelpers.getConfigVal("symbolcotcommercials"), "_W.txt")),
Helpers.getPath(_destFolder, String.Concat(ConfigHelpers.getConfigVal("symbolcotlargetraders"), "_W.txt")),
Helpers.getPath(_destFolder, String.Concat(ConfigHelpers.getConfigVal("symbolcotsmallspeculators"), "_W.txt")),
Helpers.getPath(_destFolder, String.Concat(ConfigHelpers.getConfigVal("symbolcotspread"), "_W.txt")));
//enable the conversion button once an Excel file is chosen
btnConvertImg.Source = getImage("button.png");
btnConvert.IsEnabled = true;
}
}
//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());
}
