Azure RateCard API and Excel Integration

  • Article

Simple challenge

We have been tasked to find out the monthly consumption cost of Azure services that we would be consuming as part of our solution. Although our production example had approximately a dozen services in it, to keep this example simple, in this blog we’ll be looking at only a couple of azure services. These are

  • 2 instances of Standard_D1 Cloud services
  • 800 GB of Geo Redundant storage, Standard IO – Page Blob/Disk

Everything in financial calculations start and end in Excel. But before we transfer these Azure service components into an Excel spreadsheet, we need to finalise some additional information. Azure service prices are dependent on the geo and regions from where they are consumed and also on the currency they are transacted in. For our calculations, we’ve assumed the following

  • Cloud Services will be instantiated from the ‘US East’ region
  • Storage will also be in the same region, which in Azure terms gets classified as ‘Zone 1’
  • The currency of transaction will be USD – ‘$’.

Plan

We will use Microsoft Excel for our calculation. This file will have two worksheets ‘Azure Rate Card’ and ‘Bill of Material’. We will copy the Azure RateCard information into the Azure Rate Card worksheet and the computation for monthly consumption will be carried out in the ‘Bill of Material’ tab.

Accessing and Downloading Azure RateCard data

We will use Azure PowerShell script to download the Azure RateCard data applicable to our azure subscription.

What is Azure RateCard API? Azure RateCard API is part of Azure Billing API service, which also includes Usage API. The RateCard API returns the list of the resources available at an Azure offer level (Pay-as-you-go, MSDN,…) and this is not specific to a customer’s subscription, so what you would get back here is the list of ALL the resources that are available for billing under an offer. More information on Azure RateCard API is available here and here.

Step 1. Configuring Authentication using the Management Portal

In order for our PowerShell client application to access the billing API data, the Azure subscription needs to be configured to allow access to this API from client applications. The process of doing this is clearly and succinctly described here – you’ll need to execute only the Steps 1 and Step 2 . Whilst creating the application, ensure that you specify https://localhost/ as the re-direct URI. You will need to substitute the in the PowerShell script. Once the application has been created, make a note of the Client-ID – the GUID number – that is assigned to the application that you added. This GUID will be used in the PowerShell script.

Whilst you are logged onto the Azure management portal, make a note of the following items

  • Subscription ID and Azure Offer-ID. Navigate to ‘- https://account.windowsazure.com/Subscriptions’ and select the subscription under which the client access configuration was added. Make a note of subscription ID and offer-ID values.
  • The primary tenant that you are authenticated against in Azure. This is the primary directory that you login to and in the old portal (manage.windowsazure.com), can be found under Settings à Subscriptions à Make a note of the Directory record next to your subscription ID

Step 2. Load and configure the PowerShell script

The complete PowerShell script is coped here. Substitute the following values in the script from the previous step

Shell Script Variable name

Format & example – your values would be different.

$adTenant

$adTenant = "microsoft.onmicrosoft.com"

$clientId

$clientId = "XXXXXXXX-XXXX-4d50-937a-96e123b13015"

$redirectUri

$redirectUri = New-Object System.Uri('https://localhost/')

$SubscriptionId

$SubscriptionId = 'XXXXXXXX-XXXX-4802-a5e6-d9c5a43c72a0'

$OfferDurableId

$OfferDurableId = 'MS-AZR-0121p'

 

Step 3. Execute the PowerShell script.

Save a copy of the script and run the script inside the ‘Windows Powershell ISE’ desktop application. After successful execution of the script, you should have the following data file under the folder ‘C:\Users\<username>\AppData\Local\Temp’

  • Ratecardoutput.txt

 

Step 4. Load the output data file inside the Excel

  • Data file – Ratecardoutput.txt – is a character delimited file and the delimiting character is chosen to be the exclamation mark (!).
  • Open Excel and create two worksheets inside it. One called ‘Bill of Material’ and another called ‘Azure Rate Card’.
  • Select the Azure Rate Card Tab in the Excel spread-sheet. Select Data Menu and click on Get-External Data – “From Text”
  • Open File dialog box is shown. Navigate to “C:\Users\<username>\AppData\Local\Temp” and open the file ‘ratecardoutput.txt’
  • You’ll be shown the Text import Wizard – Step 1 of 3 screen. - Select Delimited option and also ‘My data has headers’
  • In Step 2, uncheck the ‘Tab’ delimiter and enter ‘!’ in the ‘Other’ box. Click ‘Finish’.
  • Data dialog box shown, ensure that the data is inserted at the top left hand row – Enter ‘ ='Azure Rate Card'!$A$1’ value in the field.
  • If this step is incorrectly executed, all the rest of calculations will be incorrect

 

Step 5. Adding computational logic

At this point, we’ve loaded the RateCard data in our Excel. We now need to add the computational logic into the ‘Bill of Material’ worksheet.

Excel implementation details

As per the requirement, we create two rows. One containing the details about Storage and other about the virtual machine required. To facilitate this look-up, we have manually substituted the relevant ‘Meter Guid’ from the previous downloads of Azure Ratecard. This is a one-time process. In order to calculate the monthly bill, Azure services from the ‘Bill of Material’ worksheet will reference the relevant record from ‘Azure Rate Card’ worksheet.

=IFERROR(VLOOKUP(E6, 'Azure Rate Card'!$A$2:$I$2000,4,FALSE), )

Explanation: ‘E’ column contains the Meter Guid. We take the value of Meter Guid and match it against the corresponding Rate in the ‘Azure Rate Card’ worksheet. For more information on VLOOKUP, please see this article

The formula used in the column J – consumption column is

=ROUND(IF(ISNUMBER(I6),H6*I6,CalculateTieredRate(H6, I6)), 2) or =ROUND(IF(ISNUMBER(I7),H7*I7*744,CalculateTieredRate(H7, I7)), 0)

The rate value obtained from Azure Ratecard API can either be in numeric or in string format. When it’s in string format, the rates are expressed as a key-value pairs. For e.g. the rate value obtained for storage is like – “0,0.095;1024,0.08;51200,0.07;512000,0.065;1024000,0.06;5120000,0.055”. This value is not suitable to calculate the consumption. Therefore, in the Consumption Cost – Monthly column, we check the value of Rate column. If its numeric, we multiply it with the Quantity and monthly hours to come up with monthly consumption cost. Otherwise we use a separate VBA formula - CalculateTieredRate - to calculate the tiered metered cost. This VBA function is copied here

We use two flavours of formula, depending upon the unit of measure. If the rate is provided as hourly charge, then we multiply the consumption with 744 (= number of hours in a month) to obtain the monthly consumption figure.

Sample excel file, with all the mentioned functionality, can be downloaded from here

 

Result:

Now you should have a fully functioning Azure monthly estimator which takes the Azure rates applicable to your offer-ID and also takes into consideration tiered meter usage. Smile

PowerShell Script

# Activate Azure Powershell module

Import-Module Azure

# TODO - Replace the following values

# =================================================

$adTenant = "microsoft.onmicrosoft.com"

# Set well-known client ID for Azure PowerShell

$clientId = "XXXXXXXX-XXXX-4d50-937a-96e123b13015"

# subscription guid

$SubscriptionId = 'XXXXXXXX-XXXX-4802-a5e6-d9c5a43c72a0'

# Set redirect URI for Azure PowerShell

$redirectUri = New-Object System.Uri('https://localhost/')

# Azure on Internal subscription

$OfferDurableId = 'MS-AZR-0121p' # Azure on Internal subscription

# =================================================

 

# Set Resource URI to Azure Service Management API

$resourceAppIdURI = "https://management.azure.com/"

# Set Authority to Azure AD Tenant

$authority = "https://login.microsoftonline.com/$adTenant"

# Create Authentication Context tied to Azure AD Tenant

$authContext = New-Object Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext($authority)

# Acquire token

 

$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

 

$ResHeaders = @{'authorization' = $authResult.CreateAuthorizationHeader()}

 

$ApiVersion = '2015-06-01-preview'

$Currency = 'USD'

$Locale = 'en-US'

$RegionInfo = 'US'

$File = $env:TEMP + '\resourcecard.json'

$OutputFilename = $env:TEMP + '\ratecardoutput.txt' # This is usually C:\Users\<username>\AppData\Local\Temp

 

$ResourceCard = "https://management.azure.com/subscriptions/{5}/providers/Microsoft.Commerce/RateCard?api-version={0}&`$filter=OfferDurableId eq '{1}' and Currency eq '{2}' and Locale eq '{3}' and RegionInfo eq '{4}'" -f $ApiVersion, $OfferDurableId, $Currency, $Locale, $RegionInfo, $SubscriptionId

 

Invoke-RestMethod -Uri $ResourceCard -Headers $ResHeaders -ContentType 'application/json' -OutFile $File

$Resources = Get-Content -Raw -Path $File -Encoding UTF8 | ConvertFrom-Json

 

# Insert the header line

$strHeaderLine = "{0}!{1}!{2}!{3}!{4}!{5}!{6}!{7}!{8}" -f "MeterId", "MeterSubCategory", "MeterRegion", "MeterRates", "MeterCategory", "MeterName", "Unit", "EffectiveDate", "IncludedQuantity"

 

# Create the header in the output file

$strHeaderLine | Out-File $OutputFilename

 

    # We create this loop to take care of tiered meter enteries in the table

  foreach($meterObj in $Resources.Meters)

  {

  # convert the darn PSCustomeObject created by ConvertFrom_Json to our dictionary object

  $meterRates = @{}

  $meterObj.MeterRates | Get-Member -MemberType Properties | SELECT -exp "Name" | % {

  $meterRates[$_] = ($meterObj.MeterRates | SELECT -exp $_)

  }

 

        $strLine = ""

 

        if ($meterRates.Count -gt 1)

  {

  $nCount = 0

 

            foreach($meterRatePair in $meterRates.GetEnumerator() | Sort -Property Value -Descending)

  {

  $strLine += $($meterRatePair.Name) + "," + $($meterRatePair.Value)

 

                if ($nCount -lt ($meterRates.Count -1))

  {

  $strLine += ";";

  }

  $nCount++;

  }

  }

  else

  {

            foreach($meterRatePair in $meterRates.GetEnumerator())

  {

  $strLine = $($meterRatePair.Value) # this line will execute only once.

  }

  }

        $strOutputLine = "{0}!{1}!{2}!{3}!{4}!{5}!{6}!{7}!{8}" -f $meterObj.MeterId, $meterObj.MeterSubCategory, $meterObj.MeterRegion, $strLine, $meterObj.MeterCategory, $meterObj.MeterName, $meterObj.Unit, $meterObj.EffectiveDate, $meterObj.IncludedQuantity

  $strOutputLine | Out-File $OutputFilename -Append

  }

 

Remove-Item -Force -Path $File

 

Description of the PowerShell shell script.

1. Start of the script gathers all the authentication, subscription, offer-id etc data.

2. It logs on to the Azure subscription using the credentials provided and obtains an authentication token.

3. Script formats the RateCard request whilst substituting the values for subscription-id, offer-id, locale, currency, Region-info.

4. Scripts invokes REST based API call using the previously obtained authentication token and formatted request.

5. On success, the return values are written to output file – ‘resourcecard.json’ and its copied to location - C:\Users\<username>\AppData\Local\Temp

6. Script then filters and picks up only the ‘Meters’ data and converts the Json formatted data into a ‘!’ – exclamation mark - character delimited file.

a. We will use this delimiting character to load the file into Excel.

7. The output of this formatted data is written to file ‘ratecardoutput.txt’ which is copied to the folder - C:\Users\<username>\AppData\Local\Temp

Excel VBA function – Calculate Storage value

Option Explicit

 

Function CalculateTieredRate(Quantity As Double, MeterRate As String)

 

    ’The assumption is that the MeterRate that is passed is already sorted in value

    ’MeterRate format is "0, 0.024;1024,0.0236;51200,0.0232;512000,0.0228;1024000,0.0224;5120000,0.055"

    CalculateTieredRate = 0

   

    Dim MeterRatePair() As String

    Dim MeterValue() As String

    Dim nNumberOfRates As Long

  

    nNumberOfRates = UBound(Split(MeterRate, ";")) ‘ ; is our delimiting characters between records

    ReDim MeterRatePair(nNumberOfRates + 1) ‘ because the number of pairs would be one greater

    MeterRatePair = Split(MeterRate, ";")

    Dim i As Integer

  

   

    For i = 0 To nNumberOfRates ‘ Its a zero bound array

             

        Dim lowerBound As Double

        Dim upperBound As Double

        Dim currentRate As Double

       

        MeterValue = Split(MeterRatePair(i), ",")

        lowerBound = MeterValue(0)

        currentRate = MeterValue(1)

       

        Dim costAtThisLevel As Double

        costAtThisLevel = 0

       

        If (i >= nNumberOfRates) Then

            ‘ we are already at the last record. there is no upper bound for us at this point

            costAtThisLevel = (Quantity - lowerBound) * currentRate

            CalculateTieredRate = CalculateTieredRate + costAtThisLevel

        Else

            MeterValue = Split(MeterRatePair(i + 1), ",")

            upperBound = MeterValue(0)

          

            If (Quantity > (upperBound - lowerBound)) Then

                costAtThisLevel = (upperBound - lowerBound) * currentRate ‘ rate of the full band

                CalculateTieredRate = CalculateTieredRate + costAtThisLevel

            Else

                costAtThisLevel = (Quantity - lowerBound) * currentRate

                CalculateTieredRate = CalculateTieredRate + costAtThisLevel

                Exit For

            End If

        End If

    Next i

End Function