Skip to content

Instantly share code, notes, and snippets.

@suan
Created July 9, 2017 19:51
Show Gist options
  • Save suan/053fcf4fed8949545275f4553177af4e to your computer and use it in GitHub Desktop.
Save suan/053fcf4fed8949545275f4553177af4e to your computer and use it in GitHub Desktop.
Generate unemployment rate vs 3-year moving average chart
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment