[AWS] Pricing

pricing.py

import requests
from openpyxl import Workbook
from string import Template

ri_url = Template('https://a0.p.awsstatic.com/pricing/1.0/ec2/region/$region/reserved-instance/$os/index.json')
ebs_url = Template('https://a0.p.awsstatic.com/pricing/1.0/ec2/region/$region/ebs/index.json')
regions = ['us-east-1','us-east-2', 'us-west-1', 'us-west-2', 'ap-east-1', 'ap-south-1', 'ap-northeast-2', 'ap-northeast-1', 'ap-southeast-1', 'ap-southeast-2', 'ca-central-1', 'eu-central-1', 'eu-west-1', 'eu-west-2', 'eu-west-3', 'eu-north-1', 'sa-east-1', 'us-gov-east-1', 'us-gov-west-1']
oses = ['linux', 'rhel', 'suse', 'windows', 'windows-std', 'windows-web', 'windows-enterprise']
wb = Workbook()
dest_filename = 'AWS_Pricing.xlsx'
ws1 = wb.active
ws1.title = "EC2 RI"


ws1.cell(row=1,column=2).value = 'Instance Type'
ws1.cell(row=1,column=3).value = 'Region'
ws1.cell(row=1,column=4).value = 'Operating System'
ws1.cell(row=1,column=5).value = 'Lease Length'
ws1.cell(row=1,column=6).value = 'RI Class'
ws1.cell(row=1,column=7).value = 'RI Purchasing Option'
ws1.cell(row=1,column=8).value = 'Upfront Cost'
ws1.cell(row=1,column=9).value = 'Hourly Rate'
row = 2

"""
for region in regions:
    for os in oses:
        response = requests.get(ri_url.substitute(region=region, os=os))
        data = response.json()

        for instance in data['prices']:
            print("Instance type", instance['attributes']['aws:ec2:instanceType'])
            ws1.cell(row=row, column=2).value = instance['attributes']['aws:ec2:instanceType']
            print("Region", instance['attributes']['aws:region'])
            ws1.cell(row=row, column=3).value = instance['attributes']['aws:region']
            print("OS", instance['attributes']['aws:ec2:operatingSystem'])
            ws1.cell(row=row, column=4).value = instance['attributes']['aws:ec2:operatingSystem']
            print("Lease Length", instance['attributes']['aws:offerTermLeaseLength'])
            ws1.cell(row=row, column=5).value = instance['attributes']['aws:offerTermLeaseLength']
            print("RI Class", instance['attributes']['aws:offerTermOfferingClass'])
            ws1.cell(row=row, column=6).value = instance['attributes']['aws:offerTermOfferingClass']
            print("RI Purchasing Option", instance['attributes']['aws:offerTermPurchaseOption'])
            ws1.cell(row=row, column=7).value = instance['attributes']['aws:offerTermPurchaseOption']
            print("Upfront cost", instance['calculatedPrice']['upfrontRate']['USD'])
            ws1.cell(row=row, column=8).value = float(instance['calculatedPrice']['upfrontRate']['USD'])
            print("Hourly rate", instance['price']['USD'])
            ws1.cell(row=row, column=9).value = float(instance['price']['USD'])
            row = row + 1

"""
ws2 = wb.create_sheet("EBS")
ws2.cell(row=1,column=2).value = 'Usage Type'
ws2.cell(row=1,column=3).value = 'Volume Type'
ws2.cell(row=1,column=4).value = 'Region'
ws2.cell(row=1,column=5).value = 'Product Family'
ws2.cell(row=1,column=6).value = 'Price'

row = 2
for region in regions:
    response = requests.get(ebs_url.substitute(region=region))
    data = response.json()

    for instance in data['prices']:
        print("Usage Type", instance['attributes'].get('aws:ec2:usageType', ''))
        ws2.cell(row=row, column=2).value = instance['attributes'].get('aws:ec2:usagetype', '')
        print("Usage Type", instance['attributes'].get('aws:ec2:volumeType', ''))
        ws2.cell(row=row, column=3).value = instance['attributes'].get('aws:ec2:volumeType', '')
        print("Region", instance['attributes'].get('aws:region', ''))
        ws2.cell(row=row, column=4).value = instance['attributes'].get('aws:region', '')
        print("Product Family", instance['attributes'].get('aws:productFamily', ''))
        ws2.cell(row=row, column=5).value = instance['attributes'].get('aws:productFamily', '')
        print("Price", instance['price']['USD'])
        ws2.cell(row=row, column=6).value = float(instance['price']['USD'])
        ws2.cell(row=row, column=6).number_format = '"$"#,##0.000_);("$"#,##0.000)'
        row = row + 1

wb.save(filename = dest_filename)