Another question on options symbols (OCC)

Brian94582
Brian94582 Member ✭✭
edited September 2018 in Investing (Windows)
I've always had some issues with the options symbol being downloaded from both Ameritrade and Schwab on OPEN, being different than the one they send on CLOSE (at expiration). 
eg on open, it's "JNJ Jun 8 2018 117 Put (Weekly)". On close, it's "JNJ180608P117".
In the last few weeks, I'm seeing a new format come into Quicken, that's proving a REAL pain to reconcile. 
On open, it's  (as above) "JNJ Jun 8 2018 117 Put (Weekly)". On close (at expiration) it comes down as "0JNJ..R880117000".

Of course, the brokers are pointing the finger at Quicken.

Can you enlighten me please.

Comments

  • K.O. (Win-Premier)
    K.O. (Win-Premier) Member ✭✭✭✭
    edited June 2018
    Hi Brian.  I have accounts at both brokers as well.   Interesting comment wrt Schwab.  In my experience Schwab always sends tickers through in OCC format (e.g. SPX 141122P00019500) for both open and closing trades.  TDA never sends the tickers through in OCC format and last I traded an option at TDA (I use that account mostly for futures trading now) it was coming through as you specify in your original post for Open trades (i.e. "JNJ Jun 8 2018 117 Put (Weekly)").  I've never seen the close format "0JNJ..R880117000" that you specify but it wouldn't surprise me from TDA.

    Are you sure Schwab is sending this new format through as well as I have had downloaded both opening and closing options trades this morning from Schwab and both come through w/ the OCC format.

    If you are sure trades in Schwab appear in Quicken as coming through with a non-OCC format then my guess is you may have linked a transaction from Schwab to an online security from TDA whereas you transacted the same option in TDA and Schwab, the TDA trade came in first and when Schwab trade came in for same option you linked it to the TDA option (security not TDA trade) of the same underlying/exp/price.  This would be logical to think it is the security and thus the security should not have to be duplicated in Quicken.  Unfortunately in this case you do need to have the option duplicated (one in the security list for Schwab and one for TDA).

    If you want to confirm what the brokers are sending you then you need to open the OFXLog file and search for the tag "<TICKER>" (no quotes of course).  This will provide the ticker symbol the broker sent to Quicken.  If you want to then find the transactions associated w/ that TICKER you need to look a couple lines above for the <UNIQUEID> tag and then search for that value.  The unique ID is going to be a CUSIP ("kind of" as options have no standard format for CUSIP other than what the broker defines as the CUSIP) so searching for the CUSIP related to that TICKER will take you to all transactions associated with that TICKER.  However, per the above if you have the same option in the file for transactions at TDA and Schwab you'll find the TICKER more than once w/ different CUSIP values which then link to the TDA and the Schwab transactions.

    Whatever is in the <TICKER> field is what the broker is sending to Quicken.

    Now all this said, TDA isn't going to change what they send to Quicken even though it does not follow the OCC Option Code standard for ticker symbols.  But going through this exercise might A) help you understand better how transactions come into Quicken from brokers and B) confirm what the broker is sending.
  • Brian94582
    Brian94582 Member ✭✭
    edited September 2018
    Thanks so much for answering this so quickly.  I had a bunch of options expiring today so they should download on Monday. I'll do more detailed research based on your suggestions and let you know what I find early next week. A quick initial search through a file I downloaded today does show stuff like this (which is related to the example I show above):

    <UNIQUEID>0JNJ..R880117000</UNIQUEID><UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
    </SECID>
    <SECNAME>0JNJ..R880117000</SECNAME>
    <TICKER>0JNJ..R880117000</TICKER>

    but I need to spend some time to put it in the context of which broker it's coming from and what the specific txn is. (ps I trade a lot of options, so when stuff doesn't come down right, it takes a lot of extra time manually to edit and reconcile)
  • K.O. (Win-Premier)
    K.O. (Win-Premier) Member ✭✭✭✭
    edited June 2018

    Thanks so much for answering this so quickly.  I had a bunch of options expiring today so they should download on Monday. I'll do more detailed research based on your suggestions and let you know what I find early next week. A quick initial search through a file I downloaded today does show stuff like this (which is related to the example I show above):

    <UNIQUEID>0JNJ..R880117000</UNIQUEID><UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
    </SECID>
    <SECNAME>0JNJ..R880117000</SECNAME>
    <TICKER>0JNJ..R880117000</TICKER>

    but I need to spend some time to put it in the context of which broker it's coming from and what the specific txn is. (ps I trade a lot of options, so when stuff doesn't come down right, it takes a lot of extra time manually to edit and reconcile)

    Yeah, when brokers don't provide good (or consistent) information it takes a good bit of time to straighten it out.  Let me know what you're seeing when you investigate further.  That example is showing the ticker as you referenced originally.  I'd be surprised if that was from Schwab and totally not surprised if it came from TDA.
  • Brian94582
    Brian94582 Member ✭✭
    edited June 2018

    Thanks so much for answering this so quickly.  I had a bunch of options expiring today so they should download on Monday. I'll do more detailed research based on your suggestions and let you know what I find early next week. A quick initial search through a file I downloaded today does show stuff like this (which is related to the example I show above):

    <UNIQUEID>0JNJ..R880117000</UNIQUEID><UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
    </SECID>
    <SECNAME>0JNJ..R880117000</SECNAME>
    <TICKER>0JNJ..R880117000</TICKER>

    but I need to spend some time to put it in the context of which broker it's coming from and what the specific txn is. (ps I trade a lot of options, so when stuff doesn't come down right, it takes a lot of extra time manually to edit and reconcile)

    I downloaded txns last night. No sign of the
    0JNJ..R8801170000 or similar problem described above. It appears this one did
    come down from TDA, and not Schwab (I specified to download only Schwab, but
    one TDA account seems to come down always even though I didn’t check the box
    for it). Can you confirm the broker’s are responsible for setting the
    <UNIQUEID>, <SECNAME> and <TICKER>, and Quicken does not do
    any manipulation of it before I see the following in the OFXlog as:

     

    <SECINFO>

    <SECID>

    <UNIQUEID>0JNJ..R880117000</UNIQUEID>

    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>

    </SECID>

    <SECNAME>0JNJ..R880117000</SECNAME>

    <TICKER>0JNJ..R880117000</TICKER>

    </SECINFO>

     

    When this
    happens, Quicken requires me to create a new ticker, manually. In looking at my
    security list, I seem to have done this for close to 60 options in the past. I
    think that all these came from TDA.  And
    then, of course, I have to manually match the ticker on close. And I'm sure it will happen again even though it didn't happen last night.

     

    It seems
    like Schwab and TDA send equities using a unique id that corresponds to the
    CUSIP, as you say.

    Options come
    down in the 0JNJ...... format, above, from TDA, and SCHOFXnnnnnnnn format from Schwab

     

     

    Other issues encountered (which may well have been
    reported by others because they've been happening for ever)

     

    ____________________________________________________________________

     

    1.     
    Expiring Short Call options come down as
    "ADDED" from TDA. This doesn't close out the option. I have to change
    it to "CVRSHRT". This happens in TDA (all the time, I think)

     

                                                                                                                                                                           

                2/7/2017  Ameritrade - Vali IRA - xxxxxxxxx         ShtSell   AXP (Weekly) Mar 10 2017 80 Call       AXP
    (Weekly) Mar 10 2017 80 Call                       SELL TRADE          0.680       -200.000  5.95         130.05     -130.05                    1.00        

                    3/13/2017                Ameritrade - Vali IRA - xxxxxxxxx         Added     AXP (Weekly) Mar 10 2017 80 Call       AXP
    (Weekly) Mar 10 2017 80 Call                                REMOVAL
    OF OPTION DUE TO EXPIRATION                  200.000                   -130.05    130.05                     1.00        

                                                                                                   

                            Sorry,
    no OFXlog for this one. The example I show was obviously one from last year that
    I hadn’t cleaned up yet.

     

               

    _________________________________________________________________

                                                                                                                                                   

     

    2.  Expiring Long Put options come in as
    "CVRSHRT". This doesn't close out the option. I have to change them
    to "SOLD" This example happened in Schwab (all the time)

     

                                                                                                                                                                                                   

                    5/16/2018                Schwab 4446-xxxx INV           Bought   COO 06/15/2018 210.00 P      COO   180615P00210000                       PUT : COOPER COMPANIES - COO 06/15/2018 210.00
    P             1.160       200.000   0.03         -232.03    232.03                     1.00        

                    6/15/2018                Schwab 4446-xxxx INV           CvrShrt  COO 06/15/2018 210.00 P      COO   180615P00210000                       PUT : COOPER COMPANIES -
    COO 06/15/2018 210.00 P                             -200.000                  232.03     -232.03                    1.00        

                                                                                   

    <BUYOPT>

                <INVBUY>

                  <INVTRAN>

                   
    <FITID>4446195220180516120000201805161445110644882289

                    <DTTRADE>20180516120000

                    <DTSETTLE>20180517120000

                    <MEMO>PUT : COOPER COMPANIES - COO 06/15/2018
    210.00 P

                  </INVTRAN>

                  <SECID>

                    <UNIQUEID>SCHOFX48386413

                    <UNIQUEIDTYPE>CUSIP

                  </SECID>

                  <UNITS>2.0000

                  <UNITPRICE>1.1600

                  <FEES>0.03

                  <TOTAL>-232.03

                  <SUBACCTSEC>CASH

                  <SUBACCTFUND>CASH

                </INVBUY>

                <OPTBUYTYPE>BUYTOOPEN

                <SHPERCTRCT>100

              </BUYOPT>

     

    </POSOPT>

              <POSOPT>

                <INVPOS>

                  <SECID>

                    <UNIQUEID>SCHOFX48386413

                    <UNIQUEIDTYPE>CUSIP

                  </SECID>

                  <HELDINACCT>OTHER

                  <POSTYPE>LONG

                  <UNITS>2.0000

                  <UNITPRICE>0.6500

                  <MKTVAL>130.00

                  <DTPRICEASOF>20180521210000

                  <MEMO>PUT : COOPER COMPANIES - COO 06/15/2018
    210.00 P

                </INVPOS>

              </POSOPT>

     

    <OPTINFO>

            <SECINFO>

              <SECID>

                <UNIQUEID>SCHOFX48386413

                <UNIQUEIDTYPE>CUSIP

              </SECID>

              <SECNAME>PUT : COOPER COMPANIES - COO 06/15/2018
    210.00 P

              <TICKER>COO  
    180615P00210000

            </SECINFO>

            <OPTTYPE>PUT

            <STRIKEPRICE>210.0000

            <DTEXPIRE>20180615

            <SHPERCTRCT>100                                                                                                                

          </OPTINFO>

     

     

     

    <CLOSUREOPT>

                <INVTRAN>

                 
    <FITID>44461952201806151200002018061603515321839120

                  <DTTRADE>20180615120000

                 
    <MEMO>PUT : COOPER COMPANIES - COO 06/15/2018 210.00 P

                </INVTRAN>

                <SECID>

                  <UNIQUEID>SCHOFX48386413

                  <UNIQUEIDTYPE>CUSIP

                </SECID>

                <OPTACTION>EXPIRE

                <UNITS>-2.0000

                <SHPERCTRCT>100

                <SUBACCTSEC>CASH

              </CLOSUREOPT>                                                                                                                                                                                          

    _________________________________________________________________

     

    3.     
    Opening order comes down as <Ticker>:           EXC
    Jun 15 2018 41 Call

    Closing order comes down as
    <Ticker>:                  EXC_061518C41

    I have to manually match the
    correct option in the "Matching Security" screen

    This happens in TDA (sometimes)

     

    <SELLOPT>

    <INVSELL>

    <INVTRAN>

    <FITID>19261023021</FITID>

    <DTTRADE>20180516155327</DTTRADE>

    <MEMO>SELL
    TRADE</MEMO>

    </INVTRAN>

    <SECID>

    <UNIQUEID>0EXC..FF80041000</UNIQUEID>

    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>

    </SECID>

    <UNITS>-6</UNITS>

    <UNITPRICE>0.25</UNITPRICE>

    <COMMISSION>7.7</COMMISSION>

    <FEES>0.1</FEES>

    <TOTAL>142.18</TOTAL>

    <SUBACCTSEC>MARGIN</SUBACCTSEC>

    <SUBACCTFUND>MARGIN</SUBACCTFUND>

    </INVSELL>

    <OPTSELLTYPE>SELLTOOPEN</OPTSELLTYPE>

    <SHPERCTRCT>100</SHPERCTRCT>

    </SELLOPT>

     

    <POSOPT>

    <INVPOS>

    <SECID>

    <UNIQUEID>0EXC..FF80041000</UNIQUEID>

    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>

    </SECID>

    <HELDINACCT>MARGIN</HELDINACCT>

    <POSTYPE>SHORT</POSTYPE>

    <UNITS>-6</UNITS>

    <UNITPRICE>0.15</UNITPRICE>

    <MKTVAL>-90</MKTVAL>

    <DTPRICEASOF>20180522120000</DTPRICEASOF>

    </INVPOS>

    </POSOPT>

     

    <OPTINFO>

    <SECINFO>

    <SECID>

    <UNIQUEID>0EXC..FF80041000</UNIQUEID>

    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>

    </SECID>

    <SECNAME>EXC Jun 15 2018 41
    Call</SECNAME>

    <TICKER>EXC Jun 15 2018 41
    Call</TICKER>

    </SECINFO>

    <OPTTYPE>CALL</OPTTYPE>

    <STRIKEPRICE>41</STRIKEPRICE>

    <DTEXPIRE>20180615000000</DTEXPIRE>

    <SHPERCTRCT>100</SHPERCTRCT>

    </OPTINFO>

     

     

    <BUYOPT>

    <INVBUY>

    <INVTRAN>

    <FITID>19496170269</FITID>

    <DTTRADE>20180615122059</DTTRADE>

    <MEMO>BUY TRADE</MEMO>

    </INVTRAN>

    <SECID>

    <UNIQUEID>0EXC..FF80041000</UNIQUEID>

    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>

    </SECID>

    <UNITS>6</UNITS>

    <UNITPRICE>0.1</UNITPRICE>

    <COMMISSION>2.7</COMMISSION>

    <FEES>0.1</FEES>

    <TOTAL>-62.8</TOTAL>

    <SUBACCTSEC>MARGIN</SUBACCTSEC>

    <SUBACCTFUND>MARGIN</SUBACCTFUND>

    </INVBUY>

    <OPTBUYTYPE>BUYTOCLOSE</OPTBUYTYPE>

    <SHPERCTRCT>100</SHPERCTRCT>

    </BUYOPT>

     

     

     

    <OPTINFO>

    <SECINFO>

    <SECID>

    <UNIQUEID>0EXC..FF80041000</UNIQUEID>

    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>

    </SECID>

    <SECNAME>EXC_061518C41</SECNAME>

    <TICKER>EXC_061518C41</TICKER>

    </SECINFO>

    <OPTTYPE>CALL</OPTTYPE>

    <STRIKEPRICE>0.00</STRIKEPRICE>

    <DTEXPIRE>20190118000000</DTEXPIRE>

    <SHPERCTRCT>100</SHPERCTRCT>

    </OPTINFO>

     

    ____________________________________________________________________

     

     

    How can I get these issues
    addressed? They've been happening for years and really should be fixed. It just
    takes me so much extra time editing when these txns come down into Quicken.
  • K.O. (Win-Premier)
    K.O. (Win-Premier) Member ✭✭✭✭
    edited June 2018

    Thanks so much for answering this so quickly.  I had a bunch of options expiring today so they should download on Monday. I'll do more detailed research based on your suggestions and let you know what I find early next week. A quick initial search through a file I downloaded today does show stuff like this (which is related to the example I show above):

    <UNIQUEID>0JNJ..R880117000</UNIQUEID><UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
    </SECID>
    <SECNAME>0JNJ..R880117000</SECNAME>
    <TICKER>0JNJ..R880117000</TICKER>

    but I need to spend some time to put it in the context of which broker it's coming from and what the specific txn is. (ps I trade a lot of options, so when stuff doesn't come down right, it takes a lot of extra time manually to edit and reconcile)

    I've been trying to get many of these issues addressed for some time.  Glad to have another user acknowledging the problems.

    I'm fairly certain Quicken does not do any manipulation of the OFXLog file but Quicken reps would need to verify this.

    wrt Closing options from TDA I should have mentioned before that in some cases the option TDA sends (CUSIP/Ticker) for the closing transaction is not the same as the option they sent for the opening transaction (thus requiring a manual match).  I have not been able to identify the circumstances under which this occurs.

    Per your note, yes, all securities come in w/  UNIQUEID which is the CUSIP.  Options do not technically have a CUSIP so brokers put whatever they want in there w/ no standard.  If there was a standard (say maybe using the OCC option symbol) much of this would go away but I'm not holding my breath.

    Expiring options and CvrShrt vs. Added.  As you noted (and logged in the linked post below from 2 years ago) TDA sends expiring options through as Add transactions and Schwab as CvrShrt.  I guess it's debatable as to which is technically correct but Quicken won't function properly when expiring options sent through as Add transactions.  Until Quicken addresses it to get Add/Remove transaction to function as CvrShrt/Sold transactions or can get TDA to send through CvrShrt/Sold we are stuck w/ it.

    https://getsatisfaction.com/quickencommunity/topics/td-ameritrade-sending-wrong-investment-transacti...

    Schwab expiring options not always accurate (i.e. coming through as CvrShrt when they should be a Sell).  I reported this one 4 years ago.  Sigh :(

    https://getsatisfaction.com/quickencommunity/topics/expiring-call-options-come-through-as-cvrshrt-tr...
  • Brian94582
    Brian94582 Member ✭✭
    edited June 2018

    Thanks so much for answering this so quickly.  I had a bunch of options expiring today so they should download on Monday. I'll do more detailed research based on your suggestions and let you know what I find early next week. A quick initial search through a file I downloaded today does show stuff like this (which is related to the example I show above):

    <UNIQUEID>0JNJ..R880117000</UNIQUEID><UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
    </SECID>
    <SECNAME>0JNJ..R880117000</SECNAME>
    <TICKER>0JNJ..R880117000</TICKER>

    but I need to spend some time to put it in the context of which broker it's coming from and what the specific txn is. (ps I trade a lot of options, so when stuff doesn't come down right, it takes a lot of extra time manually to edit and reconcile)

    Thanks very much for your quick reply. As you suggest, these are not very difficult fixes. There obviously aren't a lot of us doing heavy option trading and encountering these issues every week. I'll have another go at TDA and Schwab through their support platforms but so far they just point the finger at Quicken. Now I understand better what's going on, it will give me a bit of extra ammunition.
This discussion has been closed.