• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching Closed orders from a list of Open orders

Im_Offset

Im_Offset

Member
Hello everyone,

I am trying to figure out a formulaic method to solve my "problem." (i.e. I don't want to use VBA just yet)

Please reference the attached spreadsheet. On the tab labeled "Master" is a table of stock market trades for this year. Columns A - H are all manually entered, and Column I is manually entered if a formula in Column J says a value is needed. The important column to highlight is Column C. This column shows if a trade is an opening trade or a closing trade.

I am tasked with matching the closing trades with their corresponding opening trade in a new table. I have created this table manually in the tab labeled "Manual Open Close". You can see in this table that every "closing" trade has a corresponding "opening" trade, but an "opening" trade does not have to have a "closing" trade (this is because the trade may still be open, or that the trade expiration date arrived and it was automatically closed without needed a closing trade).

I want to find a way to create this new table using formulas, and I am using the tab labeled "Formula Open Close" to do that. Creating the list of "opening" trades was fairly straight forward using an array formula in Column A to list the trade number of each opening trade. However, is it possible to use a formula in Column L to search for closing trades on the list in the "Master" tab, and then list the trade number of each closing trade to the appropriate opening trade?

To be a matched "pair" (i.e. for the closing trade to be listed with the correct opening trade), the following conditions must all apply:
1) the sum of the contracts in the opening trade and the closing trade must = 0
2) the Call/ Put information (columns E and P) must be the same
3) the Strike levels (columns F and Q) must be the same
4) the Exp Date (columns G and R) must be the same

You will know if the closing trade is properly matched, because Column T will show "Error" if there is a problem with the information in the closing trade section.

So...is there a super formula that can do this?

Thanks!

Clearly....I am Offset :)
 

Attachments

How did you manually differentiate? Or was it arbitrary assignment?

One workaround you can do, is do the formula to do majority of work. Then use CF or helper column to identify duplicate Trade Number and manually adjust those...

Helper column formula would be something like below.
=COUNTIFS($L$4:$L$97,L4,$L$4:$L$97,"<>"&"*")
 
it was an arbitrary choice. I could have put trades 111 & 112 in either row. I just put them in that order.

Let me see if I understand that helper formula and apply it.
 
Adjusted formula from Col M to S (now it just pulls data based on value in Col L).

So if you see 2 or greater value in column U. That indicates duplicate value in Col L.

See attached.
 

Attachments

You must log in or register to reply here.
Back
Top