The Question and Answer Board

[Koikaze]

Thank you, Matt.

The raw data consists of option quotes, downloaded from the CBOE. The first three lines (not shown) will be discarded. From the remaining data (shown below) only the CBOE quotes (those identified by -E in the strike) will be used. The expiration and strike price must be extracted, separately, from the first field.

I've followed the suggestions offered by the CBOE to get the data into a spreadsheet. My problem is trying to massage it. I've been entering the data I want by hand, but that approach has a few drawbacks. If I can find a way to massage the full data, it would be a benefit.

The data to be massaged:

02 May 25.00 (GE EE-E),5.80,pc,6.60,6.90,0,99,02 May 25.00 (GE QE-E),0.05,pc,0,0.10,0,193,
02 May 25.00 (GE EE-A),0,pc,6.60,6.90,0,99,02 May 25.00 (GE QE-A),0,pc,0,0.15,0,193,
02 May 25.00 (GE EE-P),6.70,pc,6.60,6.80,0,99,02 May 25.00 (GE QE-P),0,pc,0,0.10,0,193,
02 May 25.00 (GE EE-X),6.30,+0.10,6.60,6.90,5,99,02 May 25.00 (GE QE-X),0,pc,0,0.15,0,193,
02 May 25.00 (GE EE-8),6.40,+0.10,6.60,6.80,105,99,02 May 25.00 (GE QE-8),0.05,--,0,0.10,500,193,
02 May 30.00 (GE EF-E),2.00,+0.25,1.90,2.05,163,8583,02 May 30.00 (GE QF-E),0.35,--,0.30,0.35,1349,25981,
02 May 30.00 (GE EF-A),1.80,+0.05,1.95,2.15,279,8583,02 May 30.00 (GE QF-A),0.45,-0.05,0.20,0.35,10,25981,
02 May 30.00 (GE EF-P),1.65,-0.20,1.90,2.05,20,8583,02 May 30.00 (GE QF-P),0.45,--,0.20,0.35,24,25981,
02 May 30.00 (GE EF-X),1.60,-0.55,1.95,2.05,40,8583,02 May 30.00 (GE QF-X),0.35,-0.05,0.25,0.35,58,25981,
02 May 30.00 (GE EF-8),1.85,+0.10,1.90,2.00,314,8583,02 May 30.00 (GE QF-8),0.35,-0.05,0.25,0.35,216,25981,

which, when extracted, looks something like this:

Calls :::::::::::::: 02 May 30.00 (GE EF-E)
Last Sale :::::::::: 2.00
Net :::::::::::::::: +0.25
Bid :::::::::::::::: 1.90
Ask :::::::::::::::: 2.05
Vol :::::::::::::::: 163
Open Int ::::::::::: 8583
Puts ::::::::::::::: 02 May 30.00 (GE QF-E)
Last Sale :::::::::: 0.35
Net :::::::::::::::: --
Bid :::::::::::::::: 0.30
Ask :::::::::::::::: 0.35
Vol :::::::::::::::: 1349
Open Int ::::::::::: 25981

I will appreciate any suggestions you can make.

Thanks,

Fred