I need to calculate some Bollinger Bands (BBs) for SwingTradeBot, which is built in Rails 4. Here’s a quick definition of Bollinger Bands:
Bollinger Bands® are volatility bands placed above and below a moving average. Volatility is based on the standard deviation, which changes as volatility increases and decreases.
So I needed to do some standard deviation calculations. I found a few Ruby gems that allow you to do statistics but I quickly ran into issues with them. The general approach of the gems is to monkey patch Array and/or Enumerable, which can cause other issues. I was getting conflicts with ActiveRecord b/c of the monkey patches redefining “sum” and there was another conflict with a different gem that I tried. There are supposedly fixes for this stuff but it just felt dirty.
Then, as I often do, I wondered if I could just get the database to do the calculation for me. If so, it would be faster that way and I wouldn’t have to go monkey patching Ruby and/or clutter my app with my own standard deviation code. It was a pretty simple thing to have PostgreSQL do the calc for me. I just needed Rails to produce a query like this:
SELECT stddev_pop(close_price) FROM prices
WHERE (stock_id = 3313 and day_number > 195 and day_number <= 215)
Seems simple enough. So here's the Rails code to do just that:
result = Price.select('stddev_pop(close_price)').where("stock_id = #{stock_id} and day_number > #{day_number - 20} and day_number <= #{day_number}").load
#Note that I couldn't do ".first" on the line above b/c that creates an ORDER By clause that PostgreSQL complains about b/c the column being ordered by is not in the GROUP clause...
standard_deviation = result.first.stddev_pop
self.upper_bb = twenty_day_moving_average + (standard_deviation * 2)
self.lower_bb = twenty_day_moving_average - (standard_deviation * 2)
Done!