Importing Text file to Excel

Sep 25, 2011 at 9:11 PM

Hi all,

Excel-DNA is such a great project.  I posted a question here several years ago and received a prompt response (thanks govert). I have used Excel DNA to price futures options. I have a general Excel question and hope one of you excel experts will be able to help me out. I want to use my excel formula to calculate implied volatility for CME corn options.  But the data I want to use isn't in a format I can manipulate.  The data is found in plain text format in this file ftp://ftp.cmegroup.com/pub/settle/stlags.

The ticker symbol is PY (there are also PYC and py1, py2 etc which I dont need). I just want to get all PY data arranged by month in an excel sheet.  

How do I parse this data so that I can use it in excel? 

 

thanks in advance for any help.

 

cheers,

Dave

Coordinator
Sep 27, 2011 at 12:48 PM

Hi Dave,

It looks like a parser with regular expressions could do a good job. Then you run through the file, processing each line, and check whether it is a 'header' line or a data line.

The regular expression library in .NET is very fast and pretty easy to use. I've sometimes used RegEx Hero http://regexhero.net/ as a great way to learn and test regular expressions.

From your Excel you could set up a ribbon button to download and process the file into some intermediate objects, and then write the result to a database or an Excel workbook from there.

-Govert