{"id":566,"date":"2014-05-02T12:39:31","date_gmt":"2014-05-02T16:39:31","guid":{"rendered":"http:\/\/michaelseneadza.com\/blog\/?p=566"},"modified":"2018-11-25T16:44:20","modified_gmt":"2018-11-25T20:44:20","slug":"calculating-standard-deviations-in-ruby-on-rails-and-postgresql","status":"publish","type":"post","link":"http:\/\/www.michaelseneadza.com\/blog\/2014\/05\/02\/calculating-standard-deviations-in-ruby-on-rails-and-postgresql\/","title":{"rendered":"Calculating Standard Deviations in Ruby on Rails (and PostgreSQL)"},"content":{"rendered":"

I need to calculate some Bollinger Bands<\/a> (BBs) for SwingTradeBot<\/a>, which is built in Rails 4. Here’s a quick definition of Bollinger Bands:<\/p>\n

Bollinger Bands\u00ae are volatility bands placed above and below a moving average. Volatility is based on the standard deviation, which changes as volatility increases and decreases. <\/p><\/blockquote>\n

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<\/a> 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. <\/p>\n

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:<\/p>\n

SELECT stddev_pop(close_price) FROM prices
\nWHERE (stock_id = 3313 and day_number > 195 and day_number <= 215)<\/code><\/p>\n

Seems simple enough. So here's the Rails code to do just that:<\/p>\n


\nresult = Price.select('stddev_pop(close_price)').where(\"stock_id = #{stock_id} and day_number > #{day_number - 20} and day_number <= #{day_number}\").load\n
\n#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...
\nstandard_deviation = result.first.stddev_pop
\nself.upper_bb = twenty_day_moving_average + (standard_deviation * 2)
\nself.lower_bb = twenty_day_moving_average - (standard_deviation * 2)<\/code><\/p>\n

Done!<\/p>\n","protected":false},"excerpt":{"rendered":"

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\u00ae 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… Continue reading Calculating Standard Deviations in Ruby on Rails (and PostgreSQL)<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","footnotes":""},"categories":[16],"tags":[21,22,23],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":561,"url":"http:\/\/www.michaelseneadza.com\/blog\/2014\/04\/17\/ruby-rails-memoization-gems-memoist-vs-memoizable\/","url_meta":{"origin":566,"position":0},"title":"Ruby \/ Rails Memoization Gems Memoist vs. Memoizable","date":"April 17, 2014","format":false,"excerpt":"I was just adding some memoization to a Rails app and I was exploring the available gems. I'd used Memoist in the past on another project but I couldn't remember why I chose it over other gems. While researching today I found the Memoizable gem and thought that it looked\u2026","rel":"","context":"In "Technology"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":830,"url":"http:\/\/www.michaelseneadza.com\/blog\/2021\/12\/20\/rails-7s-load_async-doesnt-play-well-with-multi-tenant-apps\/","url_meta":{"origin":566,"position":1},"title":"Rails 7’s load_async Doesn’t Play Well with Multi-Tenant Apps","date":"December 20, 2021","format":false,"excerpt":"Disclaimer: I can't speak for all multi-tenant apps but I suspect the vast majority of those which use separate DB schemas will run into the same issue I ran into. I was excited to try out the new Relation#load_async feature in Rails 7 to speed up some pages on SwingTradeBot.\u2026","rel":"","context":"In "Technology"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":574,"url":"http:\/\/www.michaelseneadza.com\/blog\/2014\/08\/06\/slow-responses-from-the-braintree-ruby-gem-try-this-fix\/","url_meta":{"origin":566,"position":2},"title":"Slow Responses from the BrainTree Ruby Gem? Try This Fix.","date":"August 6, 2014","format":false,"excerpt":"A few weeks ago I was tasked with trying to mitigate some timeout issues in a client's Rails app making BrainTree calls. This was becoming more of a problem as the client's users built up more & more history in BrainTree. Apparently you can't paginate the results or ask BrainTree\u2026","rel":"","context":"In "Technology"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":557,"url":"http:\/\/www.michaelseneadza.com\/blog\/2014\/04\/06\/ive-finally-found-a-rails-4-x-blogging-engine-gem\/","url_meta":{"origin":566,"position":3},"title":"I’ve Finally Found a Rails 4.x Blogging Engine \/ Gem","date":"April 6, 2014","format":false,"excerpt":"I can't believe how difficult it's been to find a good solution for plugging a simple blog into an existing Rails app. I wanted to add a blog to SwingTradeBot, the new site I'm building but most answers to this question that I've found say to either use RefineryCMS or\u2026","rel":"","context":"In "Blogging"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":125,"url":"http:\/\/www.michaelseneadza.com\/blog\/2003\/07\/30\/cuba_island_of_music\/","url_meta":{"origin":566,"position":4},"title":"Cuba: Island of Music","date":"July 30, 2003","format":false,"excerpt":"Afro-Cuban music fans may want to pick up the documentary Cuba: Island of Music. (You can view the trailer there.) Here's what it's about: The film is a behind-the-scenes look at Afro-Cuban music\u2019s role in the daily life of Cubans. Gary Keys (Memories of Duke\u201d and \u201cDizzy\u2019s Dream Band\u201d) brings\u2026","rel":"","context":"In "Movies"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":89,"url":"http:\/\/www.michaelseneadza.com\/blog\/2003\/07\/19\/chuck_d_is_a_signifying_monkey_wtf\/","url_meta":{"origin":566,"position":5},"title":"‘Chuck D is a Signifying Monkey’… WTF?","date":"July 19, 2003","format":false,"excerpt":"This has to be the dumbest shit I've read in a very long time - Chuck D is a signifying monkey. The author of that article, Al Barger, made several statements that must be refuted. The first of which is this: The greatest act in rap history, by any reasonable\u2026","rel":"","context":"In "Music"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts\/566"}],"collection":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/comments?post=566"}],"version-history":[{"count":2,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts\/566\/revisions"}],"predecessor-version":[{"id":794,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts\/566\/revisions\/794"}],"wp:attachment":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/media?parent=566"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/categories?post=566"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/tags?post=566"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}