Splitting Data Using Shared Columns

blu817 · Mar 26, 2018

Hi
I’m hoping someone can help with this. For those who look at option chains for stocks it will make a lot of sense. I pasted a simplified version of the data below. I have a set of data when exported there are two shared columns (columns D and E) so on the left side it shows the prices for calls and on the right show the prices for puts. For example when looking at row one the 03/29/2018 expiration has 2 sets of data, the 40 strike C has a price of $1 and Put price of $40. I want to take the Put prices and add them to end of the call prices. The number of expiration day and strike’s vary which adds another layer to this. I am looking for a macro or suggestion on how I can split the data up, anything at this point would be great.

Product	Current Price	Previous Price	Strike Price	Series Dates	Current Price	Previous Price	Product
Call	1	1.1	40	3/29/2018	40	38.1	Put
Call	2	2.1	41	3/29/2018	39	39.1	Put
Call	3	3.1	42	3/29/2018	38	40.1	Put
Call	4	4.1	43	3/29/2018	37	41.1	Put
Call	5	5.1	44	3/29/2018	36	42.1	Put
Call	6	6.1	45	3/29/2018	37	43.1	Put
Call	7	7.1	46	3/29/2018	38	44.1	Put
Call	8	8.1	47	3/29/2018	39	45.1	Put
Call	9	9.1	48	3/29/2018	34	46.1	Put
Call	10	10.1	49	3/29/2018	35	47.1	Put
Call	11	11.1	50	3/29/2018	36	48.1	Put
Call	12	12.1	40	6-Apr	33	49.1	Put
Call	13	13.1	41	6-Apr	32	50.1	Put
Call	14	14.1	42	6-Apr	31	51.1	Put
Call	15	15.1	43	6-Apr	30	52.1	Put
Call	16	16.1	44	6-Apr	29	53.1	Put
Call	17	17.1	45	6-Apr	28	54.1	Put
Call	18	18.1	46	6-Apr	22	55.1	Put
Call	19	19.1	47	6-Apr	37	56.1	Put
Call	20	19.1	48	6-Apr	38	56.1	Put
Call	21	19.1	49	6-Apr	39	56.1	Put
Call	22	19.1	50	6-Apr	40	56.1	Put

Peter_SSs · Mar 27, 2018

blu817 said:
For those who look at option chains for stocks it will make a lot of sense.

.. and for those of us that don't (probably the vast majority of us) it makes little sense at all so you would need to spell it out in detail.
You talk about "40 strike C" What does that mean?
Row 1 has a "Put price of $40". Is that the first 40 in the row or the last 40 in the row?

Could you post the data again and show us the expected result(s) as well and explain how you got it/them manually? (My signature block below has a link for clearer ways to post your sample data)

vcoolio · Mar 27, 2018

@Peter,

Cross posted here:-

https://www.excelguru.ca/forums/showthread.php?8889-Splitting-Data-Using-Shared-Columns

Cheerio,
vcoolio.

blu817 · Mar 27, 2018

Sorry if the paste is all weird.

The way to read the original data would be for 03/29/2018 the 40 strike call is priced at $1 and the 40 strike put is valued at $40. Hopefully that makes more sense when the date and strike price is shared. Both call and put would expire on 03/29/2018.

Product	Current Price	Previous Price	Strike Price	Series Dates
Call	1	1.1	40	3/29/2018
Call	2	2.1	41	3/29/2018
Call	3	3.1	42	3/29/2018
Call	4	4.1	43	3/29/2018
Call	5	5.1	44	3/29/2018
Call	6	6.1	45	3/29/2018
Call	7	7.1	46	3/29/2018
Call	8	8.1	47	3/29/2018
Call	9	9.1	48	3/29/2018
Call	10	10.1	49	3/29/2018
Call	11	11.1	50	3/29/2018
Put	40	38.1	40	3/29/2018
Put	39	39.1	41	3/29/2018
Put	38	40.1	42	3/29/2018
Put	37	41.1	43	3/29/2018
Put	36	42.1	44	3/29/2018
Put	37	43.1	45	3/29/2018
Put	38	44.1	46	3/29/2018
Put	39	45.1	47	3/29/2018
Put	34	46.1	48	3/29/2018
Put	35	47.1	49	3/29/2018
Put	36	48.1	50	3/29/2018
Call	12	12.1	40	6-Apr
Call	13	13.1	41	6-Apr
Call	14	14.1	42	6-Apr
Call	15	15.1	43	6-Apr
Call	16	16.1	44	6-Apr
Call	17	17.1	45	6-Apr
Call	18	18.1	46	6-Apr
Call	19	19.1	47	6-Apr
Call	20	19.1	48	6-Apr
Call	21	19.1	49	6-Apr
Call	22	19.1	50	6-Apr
Put	33	49.1	40	6-Apr
Put	32	50.1	41	6-Apr
Put	31	51.1	42	6-Apr
Put	30	52.1	43	6-Apr
Put	29	53.1	44	6-Apr
Put	28	54.1	45	6-Apr
Put	22	55.1	46	6-Apr
Put	37	56.1	47	6-Apr
Put	38	56.1	48	6-Apr
Put	39	56.1	49	6-Apr
Put	40	56.1	50	6-Apr
Call	23	20.1	40	20-Apr
Call	24	21.1	41	20-Apr
Call	25	22.1	42	20-Apr
Call	26	23.1	43	20-Apr
Call	27	24.1	44	20-Apr
Call	28	25.1	45	20-Apr
Call	29	26.1	46	20-Apr
Call	30	27.1	47	20-Apr
Call	31	28.1	48	20-Apr
Call	32	29.1	49	20-Apr
Put	30	56.1	40	20-Apr
Put	31	56.1	41	20-Apr
Put	32	56.1	42	20-Apr
Put	33	56.1	43	20-Apr
Put	34	56.1	44	20-Apr
Put	35	56.1	45	20-Apr
Put	36	56.1	46	20-Apr
Put	37	56.1	47	20-Apr
Put	38	56.1	48	20-Apr
Put	39	56.1	49	20-Apr

Peter_SSs · Mar 28, 2018

Assuming original data is in columns A:H and results can go in columns J:N, try this in a copy of your workbook.

Code:

Sub Call_Put()
  With Range("J2:N" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -9).Value
    .Offset(.Rows.Count).Value = Application.Index(.Offset(, -6), Evaluate("row(1:" & .Rows.Count & ")"), Array(5, 3, 4, 1, 2))
    .Columns(5).EntireColumn.NumberFormat = "dd/mm/yyyy"
    .Rows(0).Value = .Offset(-1, -9).Value
    .EntireColumn.AutoFit
    .EntireColumn.Sort Key1:=.Columns(5), Order1:=xlAscending, Header:=xlYes
  End With
End Sub

blu817 · Mar 28, 2018

Thank you for the replying, for some reason in the previous price column its pasting dates. I tried looking at the code and can't figure it out.

Product	Current Price	Previous Price	Strike Price	Series Dates
Call	1	01/01/1900	40	29/03/2018
Call	2	02/01/1900	41	29/03/2018
Call	3	03/01/1900	42	29/03/2018
Call	4	04/01/1900	43	29/03/2018
Call	5	05/01/1900	44	29/03/2018
Call	6	06/01/1900	45	29/03/2018
Call	7	07/01/1900	46	29/03/2018
Call	8	08/01/1900	47	29/03/2018
Call	9	09/01/1900	48	29/03/2018
Call	10	10/01/1900	49	29/03/2018
Call	11	11/01/1900	50	29/03/2018
Call	12	12/01/1900	40	06/04/2018
Call	13	13/01/1900	41	06/04/2018
Call	14	14/01/1900	42	06/04/2018

Peter_SSs · Mar 28, 2018

Try adding the blue line where shown

Rich (BB code):

Sub Call_Put()
  With Range("J2:N" & Range("A" & Rows.Count).End(xlUp).Row)
    .Columns(3).EntireColumn.NumberFormat = "General"
    .Value = .Offset(, -9).Value
    .Offset(.Rows.Count).Value = Application.Index(.Offset(, -6), Evaluate("row(1:" & .Rows.Count & ")"), Array(5, 3, 4, 1, 2))
    .Columns(5).EntireColumn.NumberFormat = "dd/mm/yyyy"
    .Rows(0).Value = .Offset(-1, -9).Value
    .EntireColumn.AutoFit
    .EntireColumn.Sort Key1:=.Columns(5), Order1:=xlAscending, Header:=xlYes
  End With
End Sub

blu817 · Mar 29, 2018

If you ever end up in Dallas TX let me know and I'll buy you a beer! Thanks for helping out

Peter_SSs · Mar 29, 2018

blu817 said:
If you ever end up in Dallas TX let me know and I'll buy you a beer! Thanks for helping out

No problem. .. and I'll keep that beer in mind.

(I forgot to mention it, but I assume that you figured out to alter the date format in the middle line of the code to suit your US format?)

Splitting Data Using Shared Columns

blu817

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

vcoolio

Well-known Member

blu817

New Member

Peter_SSs

MrExcel MVP, Moderator

blu817

New Member

Peter_SSs

MrExcel MVP, Moderator

blu817

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Splitting Data Using Shared Columns

New Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock