top of page

Doing It Yourself

After I complete a client’s tax return or trading forms I get asked many times, “how did you do that?”  Which typically translates into “I would rather try it myself than pay you to do it.”  And I don’t blame them … I would rather do my own appendectomy just to save some money.


If you are trading options successfully you have the ability to prepare your own tax return and probably your own Form 8949.


So how do I do it?  Well, the first thing is to get all the transaction records into a common format.  Below is what the record format should look like with a description of the fields:

Next, it is important to sort the transactions by Trade Date and then by Ticker.


Many brokerages use different descriptions (errors) that may vary among the same option.  You need to identify “one-offs” by reconciling the transactions on your file to the open positions on your year-end statement.


For every stock or option you need to have an opening and closing transaction whether you are selling short or buying stock or opening or closing options.  The only non-matching opens will be on your year-end open position report from your broker.

​

Note:  Some data providers do not include a closing option transaction (BTC or STC) in their downloads upon expiration ... the option just disappears.  A closing transaction needs to be generated so that the opening transaction will not be recognized as “Open” at year-end.


For the do-it-yourselfers, I have developed a template that will recognize and add a closing transaction for your unmatched open position upon expiration.  You can watch a short video of it in operation below.

Adding Closing Transactions

This Template is available by request via email

Conversion Function

Below are the stock/option descriptions used by several brokerages and third-party reports.  If you would like to be able to convert your brokerage’s description into an OSI symbol, simply download the Excel function associated with your brokerage, add it to a VBA module in Excel and you will be able to convert their description into an OSI symbol.


If you use a brokerage not listed, please contact me and provide a download of some transactions and I might be able to provide a OSI Conversion function.

Click on Broker's logo to download

Downloaded Symbol/Description from Broker Should Look Like This:

Also see Charles Schwab below

SNDK Jun10 36 Put
SNDK JUN10 36 PUT
SNDK - SANDISK

VCLK Feb 16 2013 20.00 Call

VALUECLICK INC

SFLY Apr 21 2017 50.00 Call

SHUTTERFLY INC

XOP 02/16/2018 37.00 C

SPDR OIL & GAS EXPLORATION & PROD

ABX 11/17/2017 16.00 P

BARRICK GOLD CORP

CTXS Aug 18 2017 72.5 Put

CITRIX SYSTEMS INC

SPY May 4 2012 138.0 Put

SPDR S&P 500

BBBY  180119C00025000

BED BATH & BEYOND INC

SFM   180119C00025000

SPROUTS FARMERS MARKET INC

Interactive Brokers information is more than a challenge to download.  The download itself is generated from a query in their Reporting section and populates columns A through BI, much of which is extraneous data.  Click here to see a sample of IB info and the template-reformatted data.

With every conversion function comes additional functions to take the OSI Symbol format and produce the following:

 

  • Expiration date contained in the OSI Symbol in mm/dd/yyyy format

​

  • Convert to a format for the description field on Form 8949

​

  • Extract the Stock Symbol from the OSI Symbol

​

  • A function called “osisymbol” that will help you to determine the proper OSI symbol for a stock or option

I Can Help!!

If you want your data converted into the same format as above but don't see your broker listed, I can help!

​

Just send me a sample of the data download and I can probably convert your data into a format you can use and no charge!  The only requirement would be that I retain rights to the code and can display it here as a download.

​

I can also reformat your entire data file and confirm the opening and closing balances if you provide:

​

  • Prior year closing statment (12/31/Y0)

  • Current year data file

  • Current year closing statement (12/31/Y1)

​

Depending on the number of trades, a small fee may apply.

​

Feel free to contact me with your data reformat needs.

bottom of page