UK HMRC MTD OAuth2 Excel/VBA demo

I’m making available as open source my VBA routines for the “Hello World” tests on the UK HMRC “Making Tax Digital” (MTD) web service which use oAuth2 and JSON. Thanks to posts from Bruce McPherson (Excel Ramblings), Tim Hall (Excel-REST), and Kyle Beachill.

Link to Excel workbook

HMRC publishes more information on Making Tax Digital
https://developer.service.hmrc.gov.uk/api-documentation
Navigation menu at the top offers: Documentation, Applications, Support
Checklist of things to do on HMRC website in order to complete these exercises
1. Read Getting Started
2. Register for an account and sign in
3. Read the Testing instructions
4. Add an application
5. Subscribe to the APIs you want. Links to test subs in VBA
Hello World API Open Endpoint
Create Test User App Endpoint
6. Get Sandbox credentials from the Application page
and paste into the fields on the Parameters sheet Application Parameters
The parameters ending in _uri are those currently defined by the HMRC API.
7. To test user-restricted endpoints, you need to create one or more test users.
The Create Test User Service (see picture on the sheet) does it manually.
Copy the generated data into the Parameters sheet at “Generated Individual”

If you’d like to do real work with this, such as submitting VAT or PAYE returns, contact me to discuss a specification and contract.

 

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA, Open Source and tagged , , , , , . Bookmark the permalink.

1 Response to UK HMRC MTD OAuth2 Excel/VBA demo

  1. stephen pegg says:

    Hi Patrick,
    Great article. With your help I can now file my own returns. See below
    Regards Steve

    AccessToken = modHMRC.GetAccessToken(“write:vat”, version)

    Status = HMRCAPIRequest(“POST”, “/organisations/vat/xxxxxxxxx/returns”, Array( _
    Array(“Accept”, “application/vnd.hmrc.” & version & “+json”), _
    Array(“Authorization”, “Bearer ” & AccessToken), _
    Array(“Content-Type”, “application/json”) _
    ), BuildVATRequest(), ResponseText)

    BuildVATRequest = “{“”periodKey””:””18A1″”,””vatDueSales””:1000,””vatDueAcquisitions””:0,””totalVatDue””:1000,””vatReclaimedCurrPeriod””:0,””netVatDue””:1000,””totalValueSalesExVAT””:1200,””totalValuePurchasesExVAT””:0,””totalValueGoodsSuppliedExVAT””:0,””totalAcquisitionsExVAT””:0,””finalised””:true}”

    or
    BuildVATRequest = “{“”periodKey””:” & period & “,””vatDueSales””:” & var1 & “,””vatDueAcquisitions””:” & var2 & “,””totalVatDue””:” & var3 & “,””vatReclaimedCurrPeriod””:” & var4 & “,””netVatDue””:” & var5 & “,””totalValueSalesExVAT””:” & var6 & “,””totalValuePurchasesExVAT””:” & var7 & “,””totalValueGoodsSuppliedExVAT””:” & var8 & “,””totalAcquisitionsExVAT””:” & var9 & “,””finalised””:true}”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s