Link to wealthfront.com

Fork me on GitHub

Thursday, April 17, 2014

Automating JavaScript Code Quality Checks

At Wealthfront, we're big advocates for automation. In general, automation saves time and ensures consistency.

One of the things we want to ensure is the quality of our JavaScript code. This is particularly important for JavaScript, given its weirdness. While this task can't be fully automated, there's some low hanging fruit available by automating linting and style checking.

Some tools for this purpose are the Google Closure Compiler, Google Closure Linter, JSLint, JSHint, JSCS, and ESLint. We decided to use JSHint for detecting potential bugs, and JSCS for automated style checking. While we're actually using Closure for compiling our JS, it wasn't flexible enough for us to use with our current codebase. ESLint looks like it'll be really good, perhaps better than JSHint plus JSCS, but since it's still new, we decided against it.

What do we do with JSHint and JSCS?

- JSHint checks for potential errors, such as using an undefined variable or forgetting a break statement in a switch block.

- JSCS enforces a common style, such as enforcing camelCase variables, always using semicolons, or requiring lines no more than 120 characters in length.

- Both checks run whenever someone checks code into a side branch, and before deploying. Engineers also are able to run the checks locally.

What won't we do with this setup?

- An automated system won't check that code is well designed (DRY, MVC, etc, although ESLint is does have DRY rule tests on their roadmap). For that, you should hire well, emphasize learning, share best practices, and hold code reviews.

- It won't check as thoroughly as the Google Closure Compiler. Since the Closure tools compile your code, they're able to analyze more thoroughly. Closure can detect more potential bugs, like functions called with incorrect parameters, or unused or unreachable code that probably indicates a bug. It can also apply JSDoc comments for additional benefits, like type checking and deprecated code. (Our JSCS validates JSDoc too, but it isn't nearly as powerful as Closure, and we rarely use JSDoc.) One disadvantage of the Google Closure Compiler is that it is less configurable than other tools. We actually compile our JavaScript with the Google Closure Compiler, and might use its code quality warnings in the future, but decided on JSHint plus JSCS for now.

If either JSHint or JSCS detects a problem, our build emails us with an explanation. For instance, while refactoring some inline JavaScript to place it in its own file, there was a variable defined in evaluated Ruby:

var shouldShowInstructionsParams = ["<%= @deposit_type %>", "<%= @deposit_amount %>"];

In the process of refactoring, the variable was renamed to a property of our w.inlineVars object, so that it wouldn't be a global variable.

w.inlineVars.shouldShowInstructionsParams = ["<%= @deposit_type %>", "<%= @deposit_amount %>"];

Unfortunately, the variable didn't get renamed everywhere it was used in the JavaScript files. This is the sort of thing that your unit tests would catch, assuming the unit tests cover it. Even without a test, though, the linter caught the bug:

    Checking style with JSHint...
    app/assets/javascripts/pages/transactions.js: line 14, col 7,     'shouldShowInstructionsParams' is not defined. (W117)
    1 error

Once alerted, it's easy to recognize the error and change shouldShowInstructionsParams to w.inlineVars.shouldShowInstructionsParams, fixing the bug.

One of the reasons we decided on JSHint and JSCS over Closure is flexibility. We're able to configure the tools to match the rules we want to enforce. For instance, here's our .jshintrc file:

{
  "bitwise": true,
  "browser": true,
  "camelcase": true,
  "curly": true,
  "eqeqeq": true,
  "immed": true,
  "jquery": true,
  "latedef": true,
  "loopfunc": true,
  "maxdepth": 5,
  "maxlen": 120,
  "maxparams": 6,
  "multistr": true,
  "newcap": true,
  "nonstandard": true,
  "sub": true,
  "undef": true,
  "unused": true,
  "globals": {
    "_": false,
    "d3": false,
    "ActiveXObject": false
  }
}

This describes how we've configured our JSHint rules. For instance, eqeqeq means we require triple equal signs over double equal signs. You can find the full list of options here: http://www.jshint.com/docs/options/

If you're looking to improve the quality of your company's JavaScript, we recommend adding automated quality checks to your build process. It's also useful for open source projects, because you'll get lots of pull requests that don't look like the rest of the project's code. Being able to say "PRs need to pass JSHint/JSCS" is a simple way to enforce consistency as well as find potential bugs.

Wednesday, April 9, 2014

Security Notice on Heartbleed / CVE-2014-0160

The Internet community learned on April 7 about the OpenSSL vulnerability CVE-2014-0160, known colloquially as Heartbleed. Many security professionals remember similar vulnerabilities in SSH, BIND, and Sendmail that pried open large chunks of the Internet Infrastructure. Heartbleed is a similar type of vulnerability, as detailed on the Heartbleed website.

We join financial institutions across the Internet in responding to this critical vulnerability and in response conducted a full security review. After this security review we confirmed that no client-facing Wealthfront systems were vulnerable to Heartbleed, as no systems are running vulnerable versions of OpenSSL.

Further Resources for Heartbleed Help

Everyone deploying production services on the Internet is working to mitigate the effects of this vulnerability. We recommend auditing all OpenSSL systems and upgrading all systems using OpenSSL library versions 1.0.1 through 1.0.1f. Here is a quick roundup of resources we found useful in our response to this disclosure:

As always, if you have any questions about the security of your Wealthfront account, contact us at support@wealthfront.com. We will continue to monitor this issue as the community and vendors investigate this vulnerability further.

Thursday, March 27, 2014

Tracking Down a Crash in iOS

One of the iOS black arts is hunting down the cause of a crash report. At the best of times they're vague and indicative of the final point of failure; rarely do they ever identify the actual root cause in any enlightening way.

So let's take a look at a stack trace from one of our crash logs:

There isn't much to go on, but two things stand out:

  1. SIGSEGV generally means something was over released, or since this is ARC, we left a dangling weak reference around.
  2. The bomb went off while a scroll view was animating; much sadness ensued.

From the method signature of the symbolicated log we can see that the scroll view was trying to message a delegate about an animation that was finishing. A quick search of our codebase revealed that none of our classes implement this method, so we've likely mishandled an object or two during the run of the app that lead to this crash.

Another clue is that all of the methods for UIScrollViewDelegate are optional, so there is likely a check to see if the delegate actually implements that selector like this:

But how can we be sure? Enter Hopper.

Hopper is a Mac App designed to poke around compiled binaries, in this case UIKit. Here's what Hopper reveals about _delegateScrollViewAnimationEnded


As you can see there is indeed a call to respondsToSelector; Hopper can even approximate a pseudo code implementation for us:

So now we can see how a dangling weak reference could cause the crash. The delegate was deallocated without setting the scroll view's delegate property to nil. Now we just need to find out which scroll view in our app could have caused this.

After a bit of sleuthing (we don't actually have any scroll views, but lots of table views) I settled on the culprit likely being a tableview that is used in one of our views. To confirm this I wrote a simple test:

Sure enough that test failed, the table view controller was indeed leaving a dangling reference as both the dataSource and delegate of the table view. By simply setting both to nil in dealloc the test is satisfied.

Make Double Sure

Crashes like this are sometimes easy to reproduce live on a device or in the iOS simulator. However, I could not reproduce this on my own and thus settled for manipulating our unit tests to see if I could craft a test that crashed with the same stack trace:

By invoking the finish selector myself I found I could duplicate the call stack that of the original crash.

I don't believe that is the right way to test this particular failure because _scrollViewAnimationEnded:finished: is private and its implementation could change in a future release. Calling it directly from the test introduces unnecessary variability in our test suite and makes the tests very brittle.

Instead our test should validate that the root cause has been addressed. Specifically it should validate that the view controller sets the delegate and dataSource properties to nil when it is deallocated. The only test we need to add for this particular crash is one we've already seen:

This small test validates that the root cause of this crash, as well as any others that may have been caused by the same issue, are now fixed.

Wednesday, March 19, 2014

Unit testing -drawRect:

One of the more challenging places to get test coverage on iOS is custom UIView subclasses that override -drawRect:. Let's examine how we do this at Wealthfront and look at an example from our iOS application.

There are a number of great reasons to implement custom UIViews, for example we have a simple custom view to draw the border around our feed icons as it has to be different colors for separate accounts. This approach greatly reduces the number of image assets we require to render the feed.

For this example we'll look at how we test the view that draws our line chart, specifically the line of the chart itself, WFLineChartView.

As test driven developers our natural inclination is to get unit tests coverage for the bit of code that actually does the drawing, i.e. -drawRect:. But therein lies the rub, how do you actually test something like -drawRect:?

Reverse Engineering -drawRect:

The first step is to understand how -drawRect: works so that our test can set up the appropriate scaffolding to evaluate if -drawRect: is functioning correctly. My first iteration looked a little something like this:

When I ran it, I was greeted with a host of error messages like this one:

That certainly looks ominous. Let’s give drawRect a valid graphics context to work with and squash that error.

Much better:

Unfortunately we're not really testing anything yet. All we've done is confirm that calling -drawRect: won't bring down the tests in some future iOS release. There's nothing to ensure that its actually drawing a line. To understand what kind of validation we might add, lets take a look at a simplified version of the drawRect implementation:

As you can see the implementation of -drawRect: simply iterates over a set of points and adds them to an instance of UIBezierPath. So our test should validate that these points are generating the right type of additions to the path. But what kind of additions are we looking for? LLDB to rescue:

It looks like UIBezierPath is composed of a simple object which details the type of event, as well as the control points for that event. In reality this is actually backed by a CGPath, and the description method prints CGPathElement objects in a readable format. One simple test would be to generate a set of known points for the graph to draw and then decompose the bezier path in to these components for validation. To accomplish this the test must be able to track all instances of UIBezierPath created during -drawRect: and store them for validation.

Enter OCMock

There are a number of ways to tell a given unit test what instances of UIBezierPath are created during an invocation of -drawRect:. For example:

  • NSNotifications that get posted to the test by the view.
  • Adding a delegate to the view that it can ask for an instance of UIBezierPath
  • A shared constructor that is used to create new instances of UIBezierPath

For our custom views that implement -drawRect: we've chosen to use a shared constructor that can return an instance of UIBezierPath.

This constructor makes it easy for our unit tests to keep track of all the bezier paths created by the view by using OCMock to replace it during the test. This also allows the test to validate the number of paths that are used by the view, thereby preventing any extraneous allocations. As a bonus, you can optionally surround it with a #define to remove the shared constructor in release builds, thereby eliminating the unnecessary invocation of objc_msg_send.

Note: It is also possible to use OCMock to mock +[UIBezierPath alloc] to return a specific instance of UIBezierPath, however this method will also be invoked by anything else that happens to draw while the tests are running. Given that the tests run inside of a live app, in the simulator or on a device, this approach is very hazardous and quite brittle. Any change in application structure or behavior (like a long running animation or load event) could end up invoking the mock's +alloc replacement resulting in undefined behavior.

Validating the UIBezierPath

Now that we've created a way for the test to track what paths are created and used in -drawRect: all we have left to do is validate that the path is constructed correctly. A word on test scope, I don't particularly care whether the system can correctly draw the UIBezierPath I construct, it's highly unlikely the user will be able to even use their device if that's broken. Therefore, the test simply needs to make sure that the path is composed of the proper components.

To help in this endeavor I created a little class to pull apart the CGPathElements into something easier to use in a test. The primary purpose is to expose properties for important values and abstract the parsing process away from the tests, greatly reduce the effort of writing new tests for engineers who don't want to spend time worrying about CoreFoundation APIs.

Now we simply validate the path that is returned, for example the sample path above could be validated like so:

To make our code a little cleaner, most of the actual validation work is done inside the WFCGPathElement class's isEqual method. All of our tests use these helpful macros to create one line validation statements for the test. This also ensures that failures have a consistent message structure.

With this simple test as a scaffold it is much easier to write other tests that evaluate boundary conditions like whole vs. fractional positioning on retina vs. non-retina devices. These conditions can easily be enumerated in a small series of tests that generate predetermined data points for the view to draw. Of course, the more complex the drawing logic the more tests you'll want to write.

Nuke the NPE in Java

Null is a pain and it is time to nuke the NPE (NullPointerException) in Java!

Actually, NPEs are trivial to fix, so they're not actually what this blog post is about.

We've all seen code like above throw a NPE, and we know the fix is trivial:


It is safe code now, that indeed eliminates the NPE, but it yields uncertainties:
  1. must rebalancePortfolio() really accommodate receiving a null account?
  2. do some accounts really have a null portfolio?
  3. should there be else blocks that do something?
Question (1) is easily addressed: throw a NPE if account is null. This is reasonable behavior - put the onus where it belongs, on calling code to handle null accounts appropriately. The answer to question (3) in this particular example is probably no. Question (2) remains an uncertainty, and guess what - we also need to iterate over holdings:


If you really wanted safe code, I guess this is the style of code you would write.  ...and then another developer would come along, see it, and mimic it. In time, null check if statements would dominate your codebase. Putting an end to this craziness is what this blog post is about.

Eliminate Null

OK, null references in Java cannot really be eliminated. However, adhering to one principle can reduce their effect and put an end to most of the craziness demonstrated above:

Do not pass null references around in code.

Obviously, "unset" is a valid and useful state for an object reference. Fortunately, facilities other than null references have been created to represent null objects:
  1. Null Object pattern, which I find to be on the heavyweight side for most scenarios.
  2. Optional<T> within Google's guava-libraries. At Wealthfront we use Optional<T> extensively, although we use our own incarnation - see A Better Option for Java blog post.
Consider a simple Person class:

Several subtleties about this class are worth calling out:
  • For simplicity's sake, all strings (including empty string, whitespace strings, etc.) are being accepted as valid names. 
  • The constructor receives firstName and lastName as String references, checks them for null, and stores them within final class fields. This makes it trivial to see that getFirstName() and getLastName() never return null. Furthermore, checking for null in the constructor creates a fail fast class which throws a NPE with a meaningful stack trace.
  • The constructor receives  middleName as an optional String and stores it as one without checking the Optional<String> reference for null. Never ever set Optional<T> references to null, and don't check them for null since doing so would raise suspicion that maybe they are sometimes null.
  • getChildren() returns an empty list - not null!
  • Callers of getFirstName(), getMiddleName(), getLastName(), and getChildren() should not check the returned value for null. Doing so would raise suspicion that maybe these getters do return null in some cases.

Reality Check

Code in the real world passes around null: libraries return null, JSON contains null, null is stored in the database, etc. Eliminating null is unrealistic, so a reasonable goal is to limit its propagation.

For example, at Wealthfront we use the TwoLattes JsonMarshaller and we use Hibernate. Neither library supports the Person class as written above, primarily due to the missing default constructor and due to the Optional<String> field (although the latter could be overcome through registration of a custom type handler).

Minor edits to the Person class produce an entity class supported by TwoLattes JsonMarshaller and by Hibernate. It is shown here without its database id and without its Hibernate mappings. An exposed default constructor and non-final fields set through reflection now open the door for null to creep in, but its public API still clarifies intention, implying that null firstName / lastName values are unexpected error cases.


Tips

In closing, here are a few suggestions related to null in Java:

  • Avoid adding null to collections. Docs for Google's guava-libraries contain some discussion here.
  • Avoid null enum values. If needed and if acceptable, add an UNKNOWN enum value (or similar).
  • Use String.valueOf(obj) instead of obj.toString().
  • Use "apple".equals(myString) instead of myString.equals("apple").
  • Prefer value types like long over reference types like Long. If Long is required, avoid setting it to null - unboxing will throw a NPE!
  • Mark variables final when possible, and initialize them to non-null objects.
  • java.util.Objects.requireNonNull() is a nice one-liner built into the Java API for throwing a NPE (with optional message) if the provided reference is null.
  • Tools like FindBugs and IntelliJ support various null related annotations as expressions of developer intent. Such annotations are used to issue warnings more appropriately during their code analysis. JSR 305 seeks to standardize such annotations.

Wednesday, March 12, 2014

iOS Development at Wealthfront

At Wealthfront everything we build is designed to move quickly. For example, it is common for new hires to deploy new code to production on their first day. Our continuous integration and deployment environment are designed so that if the tests pass we ship. This, of course, is actually quite normal for a Silicon Valley startup with a dedicated team of talented engineers. We believe that's just how things should work.

Unfortunately the same cannot be said for iOS development in general where that cadence is, strictly speaking, atypical. App Store friction aside most organizations are challenged to ship an update to their iOS app every month, let alone every week or every day. Yet that's exactly what we can do here. In the time it took Apple to approve our first release, we shipped four significant updates to the app internally.

Let's take a look at how we've achieved such rapid cadence with Wealthfront for iOS.

The Wealthfront Way

Our dedication to effective continuous integration, test driven development and painless deployment were a big factor in my decision to join the company. I was actually quite excited to apply the same paradigms and methodology to our mobile initiatives. Using the existing infrastructure as a model I identified several important goals:

  • High quality tests should be easy to write
  • Tests should fail when something breaks, and pass when nothing is broken
  • Continuous Integration should create "production" ready builds
  • Establish a "master" stable development cycle where we can release immediately without periods of "stabilization" or "convergence"

Write Great Tests

This might sound obvious, the better the tests are the better the final product will be. However in a high velocity development environment the tests have to be as easy as possible to write. At Wealthfront we use a number of tools to facilitate rapid test iteration like OCMock and CoreData. Both provide elegant and scalable solutions to the core challenges of creating a scalable test framework.

OCMock is the Objective C equivalent of JMock. It's a mock object framework that allows engineers to separate the concerns of individual tests and ensure that each test is only executing the specific code under test. Leveraging a framework like OCMock in our continuous integration test suite greatly reduces the number of spurious or accidental failures by ensuring that tests are very tightly scoped. The natural corollary is that test results are consistent and "believable", so the build server's emails are never ignored or written off as "just having a bad day".

Our app uses Core Data under the covers to cache and organize data from our API server. Electing to use Core Data was a conscious decision based partly on its ability to be used in a test framework. At Wealthfront we believe that data driven testing is essential to our ability to validate our code. Therefore whatever persistence framework we chose had to integrate seamlessly with our test suite.

With Core Data data driven tests are actually easier to write than many alternative solutions because the test can simply configure a managed object context with the desired data and pass that context to the object under test. This type of fixture facilitates easy case enumeration (from common to boundary) with reliable and consistent results.

Integrate Continuously

Continuous integration with iOS has, until recently, been rather challenging. The Internet is full of workarounds and scripts for making solutions like Jenkins play nicely with Xcode and the iOS Simulator. But with the release of iOS 7 and OSX 10.9 Apple included a native way to support continuous integration, Xcode Server. We use Xcode server to pull changes from our Git repository and run them against the simulator and real hardware.

As with the other projects at Wealthfront our build server is at the heart of our continuous integration suite. After a pull request is approved and merged to the master branch of our git repository Xcode Server runs four different bots:

  • Our unit test suite against the app store build
  • Our unit test suite against the internal build
  • A device ready app store build
  • A device ready internal build

The unit test bots give us coverage over our production and internal builds while the other two build production ready archives that can be deployed to devices.

Stable Continuous Deployment

With this infrastructure in place we have been able to reliably hit a steady one week cadence, where improvements and even smaller new features are released at the end of each week to our employees. At the time of this writing our test framework has ~400 unit tests which evaluate everything from our parsing / caching code to custom implementations of -drawRect:. The tests take ~6-7 seconds to run per instance; the entire suite takes less than a minute on all the flavors of the iOS simulator and a few choice bits of real hardware we keep hooked up to the build server.

We've also been able to maintain a very high quality bar. For example our first internal beta had zero reported bugs, an achievement we have since repeated many times. Of course that doesn't mean there aren't any bugs in our code, they were just very difficult to find and reproduce, as they should be with well tested code.

Similar to our other projects we've achieved a master stable release cycle where changes that are successfully built by the build server after being merged to master can be shipped to the App Store. Of course, that doesn't mean we ship every passing build to the App Store. But we have the infrastructure and tools in place to know that we could if we had to.

In all we are quite happy with we have achieved thus far and we'll be sharing a lot more about our test infrastructure and iOS development at Wealthfront in general in the coming weeks. Until then please let us know your thoughts and questions in the comments.

Tuesday, March 4, 2014

Marketside Chats #4: Costs of replicating an S&P 500 portfolio

What are stock indexes?

(For a stock index, this is the correct plural - not "indices".)

Wikipedia says it is "a method of measuring the value of a section of the stock market". An index includes stocks
  1. that are similar in some meaningful way. Examples: all technology stocks, the biggest 100 stocks trading at the NASDAQ stock exchange, all stocks trading at New York Stock Exchange.
  2. with meaningful weights. One accepted way is "market-capitalization-weighted"; e.g. Apple has 100x the market value of US Steel, so it will have a 100x bigger weight in such an index. Other ones are equal-weighted and, in the case of DJIA (Dow Jones Industrial Average) price-weighted.
Why do they exist?

Most people use them as reference points. In news & discussions, "the market was up today" typically means the Dow Jones Industrial Average (DJIA) was up. This index includes 30 large stocks, and despite its deficiencies, it is the de facto most used index. Though most stocks are correlated, it is always possible for one subset of the market to go up and another to go down, so there's never a single definition of "the market is up".

However, indexes are proprietary products. The creator of an index makes money licensing it to others. In a way, the real reason for their existence is that the creators want to make money. However, to do so, they need to create an index that others will want to mimic. As a counterexample, if I create an index that includes all stocks that start with a vowel, I cannot license it to any mutual fund, because nobody will want to match it.

There are many financial instruments (e.g. mutual funds, and exchange traded funds - ETFs) that attempt to mimic the performance of well-known indexes. The investing public likes those instruments because they provide exposure to a wide, diversified set of stocks. In addition, some are cheap to own, i.e. their expense ratio (annual fees as a %) tends to be low. Finally, some are also cheap to trade: the spread (ask price minus bid price) may be only $0.01 on average for e.g. SPY, an ETF that tracks the S&P 500 index (SPX), which currently trades around $185 - so less than 0.01% of its price.

Market practitioners typically follow SPX, because it is covers more stocks (500) than DJIA (30), but also because it uses a more reasonable weighting methodology. (*1)

Index vs fund that matches an index

An index is a calculation. You cannot trade an index directly. What you can trade in most cases is:
  1. financial instruments (ETFs, mutual funds) that attempt to match an index. However, these have fees, and are subject to 'tracking error', as it is very difficult to hold all stocks in the index at exactly the right proportions, and to buy or sell stocks when they enter/exit the index while doing it at the same price used in the index calculation. That is, the performance of an ETF matching an index will not be 100% the same as that of the index itself.
  2. futures contracts, which are essentially bets on how high/low an index will go in a certain period of time. These also cannot match an index perfectly, although for different reasons. The chief reason is temporary supply/demand dislocations that may cause them to trade away from their fair value (beyond the scope of this post). Also, futures contracts are harder for retail traders (i.e. you and me) to trade, and they cover fewer indexes than ETFs do.
For instance, SPY is an ETF that you can actually buy and hold in your retail brokerage account.

Replicating a portfolio 

One is said to have a position that is...
  • long N shares, when one owns N shares of a security
  • short N shares, when one owes N shares of a security
  • flat (or zero), if neither is true
A portfolio is a set of positions. A replicating portfolio is a portfolio that behaves (almost) the same. For example, if portfolio A holds $1m of a single security that tracks SPX (e.g. the SPY ETF), and portfolio B holds $1m of the same 500 stocks in SPX & at the same weights (e.g. 50 Apple, 20 GOOG, etc.), then B is a portfolio that replicates A.

Replicating SPX
In some cases, you'd want to replicate an SPX portfolio with all 500 stocks. "Why" is beyond the scope of this article. Ignoring the real-life constraint that you cannot buy fractions of a share, you would accomplish this by splitting your money on all 500 stocks in the same proportion as SPX.

Nothing too complicated so far. However, how can one compare the trading costs of buying, say, $100k of SPX vs $100k of the underlying portfolio? If needed here, see marketside chat #1 for the definitions of bid price, ask price, fair value, and half-spread. A reasonable approach is to compare the spread of an instrument that tracks the SPX (e.g. the SPY ETF)  (*2) against all 500 spreads for the SPX stocks, weighted by each stock's relative weight in the index. E.g. if AAPL (Apple) makes 3% of the SPX index, its spread matters much more in the final result than that of a small stock.

Methodology of comparison

For each individual stock, you need to use some average of its spread over the last few days, as it may jump around.

A basis point is 0.01%. It's widely used in trading only because it is faster to say "three bips" than saying "zero point zero 3 per cent", and because the human brain can process small whole numbers more intuitively.

SPY has a spread of almost 1 cent (*3), so $0.01 / $185 ~= 0.5 bps. What is the statistical distribution of spreads for the 500 US stocks? Using some unimpressive but expedient awk, we get:

awk '{s=int(100*$5)/100; print( (s<0.1) ? s : "0.10 or more")}' spx_all.2013-08-01.final | sort -rg | uniq -c
 22 0.10 or more
  4 0.09
  5 0.08
  7 0.07
 12 0.06
 12 0.05
 17 0.04
 30 0.03
 65 0.02
326 0.01

These are counts of spreads that are rounded down. So 500-326=174 stocks have average spreads that are 2 cents or more.
However, a more natural way to compare spreads across stocks is to express them as a fraction of stock price. A similar awk commands yields (in bps):
 17 10 or more
  5 9
 11 8
 23 7
 44 6
 40 5
 87 4
135 3
108 2
 30 1

The (unweighted) average of the distribution above is ~4.5 bps (computation not shown). However - and here is a key market insight - the stocks that have high weights in SPX (e.g. AAPL) tend to be large companies, which are heavily traded and have smaller % spreads than average. The correctly weighted spread is therefore 3.2 bps, vs. 0.6 bps for SPY, so ~5x more. 

This analysis assumes that one trades a small enough size to avoid market impact (see marketside chat #1). Also, this comparison is not applicable to retail trading, as the costs for an individual retail investor would likely be completely dominated by commissions; that is, the cost of buying $1m of SPY is way more than the cost of a few hundred individual stock trades.



(*1) SPX weighs stocks based on the value of the company they represent. Instead, the DJIA is a straight sum of the nominal prices of stocks, which are somewhat arbitrary. Example: AA (Alcoa) trades around $12, while MMM (3M) trades around $130. However, the market capitalization (stock price times number of shares in the company) is $13b for AA and $90b for MMM. So the market cap of MMM stock is ~7x that of AA, while its stock price (and therefore its weight in DJIA) is 130/12= ~11x > 7x. Using this reasoning, MMM has a higher weight in DJIA than market-cap weighting would imply.

(*2) If e.g. a stock today had an earnings announcement, it would be more volatile, and market participants should demand a bigger price 'cushion' when trading it, resulting in higher spreads in the market. Therefore, it is better to take the average over many days, so as to smooth out this effect.

(*3) In the US, stocks priced over $1 must be quoted in prices that are increments of 1 penny. This may be the subject of a future marketside chat, but for now consider the two extreme cases:

  1. If the minimum quote increment is 10^-9, and Alice is bidding $10 + 10^-9, then Bob has very little to lose if he sends an order to buy at $10 + 2*10^-9; he's only improving the market by a tiny amount), while if a market order comes to sell at the bid, Bob will get to buy (and make the 'half-spread') and Alice wouldn't. The result would be endless "pennying", i.e. sending of orders a tiny bit better than the previous ones.
  2. If the minimum quote increment were $50, then (assuming fair value is around $175), there would be tons of orders to buy at $150 and sell at $200, as everyone would want to buy a $175 stock at the cheaper price of $150. Conversely, few would want to "cross the spread" (e.g. sell at $150 or buy at $200) because that would be giving away too much value. Therefore, there would be very little to no trading activity.