Change impoted table data into one row of data?

[Bret1]

I am seeking to import the data from this webpage http://bigcharts.marketwatch.com/qui...m&showAll=True and have Excel realign the table data into one row for each "Expiration Month" in the table. I am only interested in the "Strike", "Last Call Price" and "Last Put Price" for each month.

Here is an example of how I would like the data for each expiration month in the table to be realigned...

June 2013 Options, (Today’s date), Strike, Last Call, Last Put, Strike, Last Call, Last Put, etc.



July 2013 Options, (Today’s date), Strike, Last Call, Last Put, Strike, Last Call, Last Put, etc.



September 2013 Options, (Today’s date), Strike, LastCall, Last Put, Strike, Last Call, Last Put, etc.



The reason I want the data reformatted into rows for each expiration, is because I plan to update the data from the web import once at the end of each day and add the new data to an additional row under each of the previous days for each month so I can create a close-of-day history for the Last Call and Put prices for each expiration month that I will eventually be able to chart.

[Dimitrov]

Does the website have a download feature whereby you can export the data into a text or .csv file? Or are you thinking that you will copy and paste from the website?

Absent this information, I think your general strategy might be, for each month, to get the strike, last call, and last put into a three column data set or table first. Then write some code that will take each row of this three column table and move them into one row (i.e., grow the row from left to right). That would take care of one month. Then do the same thing for the next month, and so on. I see some blanks in the data, based on the link you give in your post, and you would need to decide what to do with those blanks (e.g., do they represent zero?) also.

[Bret1]

Thanks for the suggestion. The website does not have an export feature that I'm aware of. I've emailed them and asked if there is a parameter I can put in the URL that will make it only pull data for a given specific month. That would help a lot, so I could import each expiration month into a separate Excel table and work with each expiration month separately.

I've attached an Excel file that allows me to import the data for any ticker symbol in case it helps.
From here, I guess I just need some code that would show only the Expiration Month, Strike, Last Call and last Put columns. Then reformat each row into one long row.

[Dimitrov]

There are some rows that contain the words "Stock Price >>". For example, in your file have a look at cell A36. Do you want data in these rows to be kept and treated like the rows that start with the words "quote"?

Also, have a look at cell A133. Not sure what happened there, but it looks like data may have been shifted one column (either to the left or to the right).

[Bret1]

Dimitrov,
Sorry, for the delay. I haven't been able to access my computer for a few days.
Thanks, for offering the help. After I started this thread, I was able to get a little further on my task and started another "similar" thread (I forgot I had this one open.)
Please take a look at it here... http://www.excelforum.com/excel-prog...n-one-row.html

Thanks!