Wednesday, January 16, 2013

Exporting Scottrade Buy/Sell Transactions to a Spreadsheet

This morning, I was trying to remember how to export transactions in my Scottrade account to a spreadsheet. I recalled having done this before.

I found this page which confirmed for me that I am correct in thinking that this can be done

Transaction History

Here are the steps I followed to export my buy and sell orders to an Excel spreadsheet:

  1. Log in to your account
  2. Choose the My Account tab at the top of the page
  3. Choose Account History from the menu on the left side of the page
  4. Allow the Transactions tab to continue to be the default choice at the top of the page
  5. Find the dates pull-down menu that has a date range, such as Last 7 Days
  6. Change the pull-down menu to Select Dates
  7. Choose a date range to the right of the pull-down menu
  8. Immiediately below the date range pull-down menu is is another pull-down menu called Transactions. Change this pull-down menu from All Types to Trades if you do not wish to be swamped with data
  9. Immediately to the right of the Transactions menu is a Security Type menu. With this menu you should be able to differentiate between Stocks or Options or other security types. Use this menu to further filter your data.
  10. Press the Go button
  11. All date-range transactions will appear on your screen
  12. If you prefer chronological order over reverse chronological order clicking on Date at the top left-hand corner of the heading for all transactions does nothing to alter the saved spreadsheet. Sorry!
  13. When all your transactions appear on the screen, press the Export to Excel button in the bottom left-hand corner just below your transactions
  14. Use your favorite utility to translate all transactions from reverse chronological order to chronological order. For Linux users, the Unix tac command works. Sorry. I don't know what to suggest for Windows users or for users on other platforms.
  15. If you do decide to reverse the chronology of the transactions, you might want to put the heading back on the first line of the file. Reversing the chronology of transactions moves the heading to the last line of the file.
  16. To place the heading back on the first line of the CSV file, use your favorite text editor. I use Vim, but you might but you might prefer Notepad under Windows or whatever text editor you like to use under your favorite operating system
  17. Examine the CSV file for transactions you have no interest in with a text editor. Many transactions are pure Cash transactions with Cash as the stock symbol. You may wish to eliminate these to eliminate excess clutter in your transaction data. See above step where you choose what kind of data you want via the Transactions pull-down menu for a better way to eliminate extraneous data. If you are not able to eliminate data with the Transactions pull-down menu, you can always do it now with a text editor.

Once the spreadsheet has been exported, you'll find that the filename includes a timestamp in YY-MM-DD-HH-MM-SS format.

It occurs to me that the spreadsheet file can really be imported into any spreadsheet. I just looked at the file and it is a CSV file which is comma separated values. In other words, the file is not exclusively an Excel spreadsheet file. It is really a file that is compatible with any spreadsheet.

Here's a Wikipedia article on CSV files if you are interested and want to know more:

Comma-separated values

Ed Abbott

Posted by at

3 comments:

  1. vingram6February 22, 2014 at 3:49 PM

    Thanks Ed. That is exactly what I did. But I selected Trades and then Options only. The list showed me what I selected (just the options) but when I export to Excel via csv file, the file contains ALL the trades within the time frame not just the options as shown on the Scottrade page. Do you know of any way to get Scottrade to just give me a csv file of what I asked for?
    Thanks,
    V. Ingram

    ReplyDelete
    Replies
    1. Ed AbbottSeptember 28, 2017 at 6:54 AM

      Hi vingram6,

      Three and a half years late and not much help, but here's my reply:

      I've not tried to export an options-trade CSV file as I do not trade options. I'm mystified as to why the CSV file would give you all the trades when you asked for options only.

      It occurs to me that maybe this is because internally Scottrade uses two layers of filtering. The first layer is the CSV file itself and the second layer is the presentation of the CSV file as a table on your screen.

      If this is true, than naturally the second layer of filtering, the presentation layer of the data in a table, would be eliminated when you export your CSV file.

      Again, not much help. All explanation and no offer of a fix.

      The reason I think it might work this way is because there are 2 pulldown menus which are used to filter transactions. The 2 pulldown menus are "Transactions" and "Security Type." It appears to me that the first pulldown menu, the "Transactions" pulldown menu, controls the final result in the CSV file. The other pulldown menu, the "Security Type" menu, may not affect the CSV file in any way if what you describe above is true.

      I've not experimented with any of this. My reply is entirely pure speculation. Since I always set my "Security Type" to "All," I always end up receiving the
      entire CSV file, regardless.

      Perhaps you have figured out a way to filter your CSV file for options only in the 3-1/2 years since you posted.

      Does the CSV file differentiate between option trades and common stock trades? I don't know since I don't ever trade options. However if the CSV file does label options in some manner, you obviously have a means to filter it via the grep command (under Linux) or some other means.

      All the best!

      Ed

      Delete
    2. Ed AbbottSeptember 28, 2017 at 8:06 AM

      Hi again vingram6,

      Looks like I made a faulty observation as to what I actually do when generating a CSV file. Ignore the
      errors in my earlier reply. Here's what I really do:

      1 -- On the first pulldown menu, the "Transactions" menu, I choose "Trades."

      2 -- On the second pulldown menu, the "Security Type" menu, I choose "Stocks."

      Now that I'm looking a bit closer, I see that the two menus working in tandem are filtering the CSV file perfectly.

      By choosing "Trades," I'm only receiving stock trades in my CSV file. So far. so good.

      Hmmm. Not so fast. I've come to a conclusion too quickly.

      I now see that the second menu, the "Security Type" menu has only one type of security type that I actually trade. I only trade "Stocks" and not "Options," "Mutual Funds," or "Fixed Income."

      Therefore whether I choose "All" or whether I choose "Stocks," I get the same result. In other words, the superset "All" and the subset "Stocks" are exactly the same thing for me.

      I'm a poor test-case for your problem. Since I trade only one type of security, stocks, than the second menu has little meaning for me.

      OK. One more observation: When I choose "options," even though I trade no options, I get an empty CSV file. My "options" CSV file is empty of all data except for one line of column headings.

      So, it appears to me, as far as I can tell, that the CSV file is filtering for options only. Of course, I'm not really a perfect test case since I trade no options.

      I wonder. Maybe this has been fixed since you last posted. To all appearances, the Scottrade interface is making a gallant attempt to filter the CSV file for options only at the time of this writing.

      In any case, I've become much more familiar with this interface than I ever was before. Thanks for writing!

      Kind regards,

      Ed



      Delete

Subscribe to: Post Comments (Atom)