Thursday, June 14, 2012

(Possible) Tool for managing Scottrade Portfolios (not OFX)

(I hope you stumbled on this post trying to find a solution for a stock portfolio management/status solution because you're frustrated with everything else out there and you're a Scottrade customer.)

I am Scottrade customer.  I have a bad habit (maybe) of wanting to watch my portfolio.  Now, I'm in it for the long haul, meaning I don't really do day trading or even monthly trading.  We usually put funds into my wife's IRA account at tax time for the deductions or, if we have some spare cash, into our joint non-retirement account.

I, too, also have an IRA account and sometimes use dividend payment amounts to purchase new stocks within the account.  But now that I have a 401K at work, any contributions I make to the account (if allowed at all) are not tax-deductible.  You can't contribute to both a 401K and an IRA.  Why would you, really, when a 401K has much higher contribution limits (and typically, employer matching) than does a self-managed IRA?  However, please take any tax-related information I provide with a grain of salt and do your own research.  I am not a CPA.

For a long time, I used Google Portfolios to watch my stocks and ETF's.  However, I became extremely frustrated with the limitations of their system.  I love Google, don't get me wrong, but Google Portfolios just doesn't provide what I want. (And who can really complain? It's free, after all.)  Here are some of the areas that I wanted improved, but haven't seen:
  • They offer imports  OFX and CSV, but they don't work very well.Best I can tell, Scottrade doesn't provide an export to OFX.  Scottrade only offers CSV exports from your account history.  
  • Their OFX import isn't perfect and can't handle all transactions types.
    (I went through many downloads and trial installations of CSV-to-OFX software (paid and free) just to end up more frustrated than when I started in the first place.)   I attempted to import a three-line Scottrade account history CSV export containing three dividend payments.  The "Preview Import" seems to show them, but once you commit, you just get:  "Some transactions in this portfolio are invalid. Edit transactions to correct errors." with Google Portfolios.
  • Google Portoflios cares pretty much nothing about dividends.Why does this matter?
    • Dividends affect account cash balances.
    • To me, dividends are a part of performance.  (A $10 purchase of a stock now at $20 a share, that's 100% growth.  But if you also received another $100 in dividends, wouldn't you say that stock is performing better than another stock at the same purchase/growth that hasn't paid any dividends?)
    • To be fair, I think you can enter dividends (or at least cash transactions) manually.  But who wants that.
  • I can pretty much guarantee you that every brokerage's data exports are going to be different.
    So, I can understand Google needing to make things a bit generic.  I use Scottrade and am going to continue to use Scottrade for the forseeable future, so that's where my focus is.  If Scottrade puts "CASH" as a ticker symbol in the export, I need to be able to deal with it.
  • I have multiple accounts I manage.
    With my IRA, my wife's IRA and our joint non-retirement account, I want to see three different account cash balances but I want to see performance of all three accounts as a combined portfolio.  (And perhaps each account individually, too.)  There are some stocks that we own that we have purchased by different accounts.  So I might have 10 shares of (F)ord in my IRA and another 30 in our joint account.
  • More things I can't think of offhand.
    It wasn't just Google that had incompatibilities with my needs.  It was pretty much every other free online or downloadable portfolio manager I tried.
So.... to the solution.  I'm a web developer.  I think I'm pretty good.  I've been developing software and websites for right about 20 years now.  So I wrote my own portfolio manager, specifically to handle Scottrade.  Before you get any further in your reading, let me state that I have nothing production ready for anyone but myself.  There's no login security yet, I run the webserver that powers it in a local VMWare setup.  There's also no separation between my accounts and anyone else's I might put in.  The purpose of this post is to gauge public interest in my developing this further.

Here are some features I've included:
  • Security
    There is never a need to provide your login information for Scottrade to us.  You don't want to, we don't want you to.  You handle the exports from Scottrade manually.  (Perhaps later we'll add an API call (if Scottrade makes that possible in the future) or some sort of screen scraping.  Personally, I'd like to have the system check my Scottrade accounts one or two times a day.  I'd like to see Scottrade offer some sort of public-key API that lets a service pull transactions, but never, ever be able to modify anything about the account.  That'd be swell!)
  • Account Creation
    If you're like me, and have multiple accounts, you can create those accounts, with any label you want. It is not necessary to reveal your Scottrade account number to the site.
  • Transaction Import:
    Handles the [current] standard Scottrade export you find under My Account (tab), Account History (left nav link).  You choose what dates you want to view, then you can click the "Export to Excel" link to receive a CSV file download.  Then in (the tentatively named) "PortfolioWatcher" (PW) system, you can import the files to the appropriate account you've created.  In addition, the import process automatically updates the stock with the 
    Scottrade account history export tool
    Scottrade account history export tool (Image courtesy Scottrade)
  • Positions Report
    This is the screen you'll look at most of the time.  Shows all your stocks, the semi-current values of each stock, what type it is (Stock, ETF, etc.) and all the values regarding investment, unrealized value, growth, growth with dividend, dividends total, average dividend, etc.  The page can be set with a customizable refresh time (15 minutes at a minimum or more) so that if you keep the page open in your browser, it will automatically update all the values as the stock prices change.  (The stock prices are updated every 15 minutes, so any refresh on the report less than that is a waste of bandwidth and webserver processing because there will be no difference shown most of the time.)

  • A "slim" version of the Positions Report
    My wife and I like to be able to see the report on our customized Google home pages, too.  There are a couple different Google page "gadgets" that allow for an iframe tag to pull content from another site.  In the slim view, the totals are at the top to avoid scrolling.

  • Stock quote updates via Google API every 15 minutes during market hoursThis system isn't meant to be realtime.  It's meant to give you an idea of how things look in your portfolio.  It answers "Am I up?", "How much am I down?" and "How's my portolio doing?" in a rough fashion.  If you need more detailed and much quicker information, try the Java app that Scottrade provides after meeting minimum account balance criteria.
  • Daily Closing Balance retrievals
    After the market closes each day, the system automatically looks at all the stocks ticker symbols it knows about (assuming some account has an active positions for it) and retrieves the balance for each and stores in our history.   Also, if I were to import a new stock that I purchased, they system will go to the earliest transaction date I imported and retrieve all possible history for that stock from that date through today.
  • History Charts (incomplete)
    The Daily Closing Balance retrievals are important to create stock position graphs.  The feature is not yet complete (drawing the charts, etc.), but will allow you to filter by one or more of your available accounts and by a given date range.  I also intend to allow multiple chart line comparisons (limit five stocks) for a given date range.  Charts would be available by most of the value fields such as by growth, growth with dividend, dividend totals, etc.

I hope this gives you an idea of what I've developed and where I'd like to go with it.  If there's enough interest by folks wanting to use the site to view their own Scottrade portfolios I'd like to continue my development and allow a few beta testers to sign up and work out the kinks once the security model is firmly in place and has passed my own QA process.

Long term, I'd like to make it a free-mium type site.  Free accounts would be limited to a single brokerage account and probably less-frequent data refreshes, such as 30 or 45 minutes instead of a 15 minute minimum.  Still functional, highly useful, but just, well, free users would hammer the server a lot less and utilizes less bandwidth.  The likely price-point would be around $8-10/month or $80-100/year (shaving off two months cost for a yearly purchase).  Another option would be to offer a certain time frame (probably 30 days) free to let folks see how great it is.

In addition, once the Scottrade portion is complete and running bug-free we could begin to offer support for other brokerage houses if people feel like what they get with other tools or a given brokerage's built in tools doesn't meet their needs.

Drop me a line if you think you might be interested in being a potential user of the system.  If I get enough response, I will focus some time on it and get it up to speed with proper security, encryption, etc.  If there is enough interest and I get it to that point, I'll likely create some "sample" portfolios so that you could see it in action and browse through the features.

- Will Belden
June 14, 2012

8 comments:

Styggiti said...

Will,

You can make contributions to both a 401k and a Roth IRA as long as you meet the income requirements. Sure the Roth is after-tax contributions, but any earnings are tax-free upon withdraw, which is the opposite of your 401k account.

Dale said...

Will, been wanting something like this. Did you ever get it up and going?

Will Belden said...

Dale, sorry I didn't reply sooner. Was out of town when I got the notification and simply forgot about it in the meantime.

Honestly, you're the only one who has stumbled across this and expressed an interest. I'm still working on the system as time permits, but it is quite slow at the moment as the FT job is keeping me a bit swamped.

Your inquiry will remain here as a comment and hopefully I'll remember to contact you once I have something worth playing with and have some security integrated in.

Thanks for posting!

Jared Swets said...

Will, I am in the exact same position you are.. was using Google Finance and am very frustrated with the experience overall.

Also find that all the information I want from Scottrade is not on one easy to view page and was hoping to change that.

I was doing some basic searching to see if scottrade had an api of some kind and found this.

I would love to have the source code for this, if that is a viable option... although if it isn't mainly php or python based I would not be able to edit it much. Also, looks like you aren't pursuing the open source model.

Will Belden said...

@Jared : Unfortunately, Scottrade doesn't have an API yet. In fact, I'm so disappointed in this, from a technical perspective, that I've already signed up for another brokerage that does. Whether I move all my funds to them or not is still up in the air. But, if I can continue to work on this system (and it's only in my spare time), I fully intend to support additional brokerages, such as eTrade, etc.

As to open source, yeah, that's definitely not my intention. Either it's for me alone (and can have all the bugs I can live with) or I'll work to do some kind of closed-source/banner-ad/subscription mechanism on it. Honestly, though, most of what it contains right now is just a bunch of math and the ability to pull the live data from where I can get it. Google's feed was killed in their latest round of product cuts, so I'm resorting to the Yahoo/YQL feeds. They are intermittent on some days, though.

Currently the system is in Coldfusion. Wouldn't mind doing some Python, but PHP is right out the window. I briefly considered a job doing PHP and was treated so poorly by the company that I now blame all PHP developers! :)

Will Belden said...

I should add that this system is running. I use it daily, just for myself. It's not commercially viable yet.

Will Belden said...

For anyone watching this thread, I've been crazy busy with "real" work, so I haven't put any time or effort into this.

As I'm getting to roll a personal 401K over to my Scottrade IRA, it's likely that I'll get back into this. My wife's travel agency (http://StingrayTravel.com) is getting a complete new website (in progress) being built by me and between the full time job, some other sites I have and the agency site overhaul, it's quite doubtful it'll be anytime this year that this site is where it needs to be.

I thank you all for your attention, and support, but, well...don't hold your breath!

Will Belden said...

Also, I spoke at length with Scottrade the other day (since I had questions about the 401K rollover) and the rep I was speaking to indicated that I'm not the only person asking about an API.

I also indicated that if the right brokerage came along with per-trade fees around $7 *and* has an API, I'd likely move all my funds!