Generate unemployment rate vs 3-year moving average chart

gistfile1.txt

#!/usr/bin/env ruby

require "json"
require "date"
require "rest-client"
require "axlsx"

BLS_REGISTRATION_KEY = "<get your own from https://data.bls.gov/registrationEngine/>"
# How many spreadsheet rows before the data actually starts
TABLE_OFFSET = 1
THREE_YEARS = 12*3

def main
  response = RestClient::Request.execute(
        method: :post,
        url: "https://api.bls.gov/publicAPI/v2/timeseries/data/LNS14000000?registrationkey=#{BLS_REGISTRATION_KEY}&startyear=2004&endyear=#{Date.today.year}",
        timeout: 999,   # they're VERY slow...
        content_type: :json, accept: :json
  )

  data = JSON.parse(response.body, symbolize_names: true)[:Results][:series].first[:data]

  monthly_rates = data.reverse.each_with_object({}) do |d, memo|
    memo[Date.parse("#{d[:periodName]} #{d[:year]}")] = Float(d[:value])
  end

  Axlsx::Package.new do |p|
    p.workbook.add_worksheet(name: "3-year Moving Average") do |sheet|
      sheet.add_row ["Month", "Unemployment Rate", "3-year Moving Average"]

      monthly_rates.each_with_index do |month_rate, i|
        sheet.add_row [*month_rate, moving_average_formula(i+1+TABLE_OFFSET)]
      end

      sheet.add_chart(Axlsx::LineChart, title: "Unemployment Rate vs 3-year Moving Average") do |chart|
        chart.start_at("E3")
        chart.end_at("W35")
        chart.add_series data: sheet["B#{1+TABLE_OFFSET}:B#{TABLE_OFFSET+monthly_rates.size}"],
          title: sheet["B1"], labels: sheet["A#{1+TABLE_OFFSET}:A#{TABLE_OFFSET+monthly_rates.size}"]
        chart.add_series data: sheet["C#{1+TABLE_OFFSET}:C#{TABLE_OFFSET+monthly_rates.size}"],
          title: sheet["C1"], labels: sheet["A#{1+TABLE_OFFSET}:A#{TABLE_OFFSET+monthly_rates.size}"]
        chart.catAxis.gridlines = false
        chart.valAxis.gridlines = false
        chart.catAxis.label_rotation = -67
        chart.catAxis.tick_lbl_skip = 12
        chart.catAxis.title = "Month"
        chart.valAxis.title = "Unemployment Rate (%)"
      end
    end

    p.serialize("unemployment_recession_indicator.xlsx")
  end
end

def moving_average_formula(row)
  start = row - THREE_YEARS + 1
  return unless start > TABLE_OFFSET

  "=AVERAGE(B#{start}:B#{row})"
end

main