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

13 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!

Raul T said...

Hi Will,

How is it going? Have you given up on Scottrade or are you still working on its API? I understand your solution is necessarily based on page scraping, or am I missing something?

Will Belden said...

@Raul, actually I have been doing nothing on this project as there hadn't been much response. (Not surprising, really, since I'm not a widely read blog!). But over the last few days, I've been putting a plan together for revising this into something modern, but with all my concepts in place, but along with all the other components I hadn't yet written (like security, performance, etc.)

You are 100% correct, though... I'm completely reliant on screen scraping. Well, that's not completely true, I guess, if I allow the user to provide the portfolio data. That's what I do for myself. I go to Scottrade, and for each of my three accounts I do a search and export the .csv, then "import" it into my system. To me, that's a huge hassle. Sure, I don't have a lot of portfolio change. I don't really buy or sell frequently. What does occur more frequently are the dividends, which is a big part of why I started writing my own system.

I have looked at a product in the past called jSoup. It's like jQuery, but for Java, and not browser-based. Would be mighty helpful in the screen scraping, except that I really don't want to get into that. In addition, I'll have to figure out how to get node.js to use jar files (I'm sure there's a way), but it's definitely not a first choice for me.

So, no update right now. Once I have my new structure in place and some progress (three months, maybe? Who knows?), then I'll have an official domain and maybe start getting users on it that are will to beta test out. Or maybe just make it it's own distributable. Not sure of my long-term path.

Raxin said...

Hi Will,

I just stumbled on this old and interesting thread.

I've been telling Scottrade for years that they need to get an API. They also need to extend the time limit on their Good Until Cancelled and Good Until Date orders. They haven't budged on either. I'm looking for another broker too. I'll miss my chart templates in ScottradeELITE though. The ability to change the chart colors and look and feel is really nice.

About your portfolio displayer, I've also been telling Scottrade for years that they need to include more stuff in their Unrealized Gain and Loss display, which would be good if your utility did, such as expected sell commission (can assume one trade for all shares), expected fees, dividends received, and foreign taxes paid, and they need to calculate all that along with the number of days that the shares have been owned (which could get complicated if you didn't buy them all at once) and the extra days you have to wait for funds to settle so you can use your money again, to give you an estimated annualized return on investment (AROI) if sold today. They currently estimate your raw dollar profit (using only number of shares, buy price, buy commission, and last price), but it doesn't help you judge your personal performance as a self-directed retirement account manager very much without calculating the dates. You really have to know the AROI to see how well you're doing. It's like those YouTube videos that say, "I made $100,000 trading stocks". So? That tells us nothing. How much money did you have to invest to make that profit, and how long did it take you? If it took you a million dollars and 10 years to do that, you suck. If it took you $50,000 and one year, you're awesome.

I currently calculate my AROI in Excel by plugging in the numbers myself. It would be nice to have a page that calculated everything for me by grabbing the data through an API. I'm not sure from your description if I would have to plug in everything myself on your page, in which case it wouldn't be much faster than what I now do in Excel.

You mentioned possibly changing to a broker that has an API. Did you? Which one?

The best thing would be if the brokers provided these kinds of pages on their own sites and in their own applications and if they allowed customers more ability to customize their own pages right on the broker's sites, and even share templates with other customers. Since most brokers don't seem to understand the hyper-plastic-customer-interface concept, we have to rely on third party volunteers like yourself using the APIs that some brokers are providing. Maybe once they see how much innovation potential is out there, they'll start feeling the pressure to support better customer tweaking in the first place so we don't have to use the APIs and third parties to get what we need.

Will Belden said...

Raxin,

So a few things. I'm in the (kinda slow process) of transferring all my stuff to TradeKing. I haven't quite pulled the trigger yet, just because I have all these forms to fill out, etc. Guess I should focus on that this week.

I really like some of the suggestions you've made (well, I read them as suggestions) like foreign taxes and maybe allowing a breakout (and more analysis) by batches purchased. I, too, have some stocks that I bought for one price, and more for another later. Right now, I'm just aggregating them together, much like you indicated the brokerages do. But I keep thinking that I'd like to know how a particular purchase has performed over another. Easy enough to do, I think.

And no, the idea isn't to replace Excel with this. I load it all as data. I just have a custom importer for the Scottrade format, put it into my database, and then I combine with a market values pull. (Right now I use a Google API for that, but it's been "shut down" for years! Energizer bunny, I guess. But it will die eventually.)

I'm sure, when I get this publicly running, there will be tons more I have to do. I'm seeing such an interest here it's very hard to ignore the potential for this.

FlyHigh said...

For this, I had been using this and it's great.
It has instructions which can help you.
Stock Portfolio Management in Excel
Just visit the site for more info.
They also have a live support and a forum to help you with your questions/concerns.