Azure RateCard API and Excel Integration
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. 
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
Comments
Anonymous
December 08, 2015
Hello, Great stuff !! Would have the same script to manage data coming from the "Resource Usage" API? I might need it in a near future. Thanks !- Anonymous
May 05, 2016
The json format is similar for both APIs so should be possible
- Anonymous
Anonymous
December 15, 2015
Thanks for sharing your experiences, scripts and examples.Anonymous
February 10, 2016
Hi, I am getting the below exeception: Exception calling "AcquireToken" with "4" argument(s): "AADSTS90014: The request body must contain the following parameter: 'client_secret or client_assertion'.- Anonymous
May 05, 2016
Suspect that you've not setup your Azure subscription to accept the RateCard API access. - Step 1 of the blog
- Anonymous