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

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.




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 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.