Download HMRC monthly exchange rates and convert to JSON/CSV

download-convert.sh

for i in 21 22; do for o in 0{1,2,3,4,5,6,7,8,9} 10 11 12; do wget http://www.hmrc.gov.uk/softwaredevelopers/rates/exrates-monthly-$o$i.XML; done; done

for i in *; do xq < $i; done | \
  jq '
    .exchangeRateMonthList | 
    (."@Period" | split(" ")[0] | strptime("%d/%b/%Y") | strftime("%d/%m/%Y")) as $from  |  
    (."@Period" | split(" ")[2] | strptime("%d/%b/%Y") | strftime("%d/%m/%Y")) as $to  | 
    .exchangeRate[] | 
    select(.currencyCode == "USD") | 
    {from: $from, to: $to, rate: .rateNew}
  ' | \
  jq -s '.'  | \
  jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'