ICP connection data access via MS Excel?

  • 2K Views
  • Last post 26 October 2018
energie posted this 11 June 2016

Hi EMI,

As an engineer, but not a programmer, I am looking for a simple way to import ICP Connection Data into Excel.

It appears there are two available data sources:

1 - For one off queries (which is typically me) -> http://www.ea.govt.nz/consumers/your-power-data-in-your-hands/my-meter

Once I query an ICP once via the website form, the data is downloadable at http://www.ea.govt.nz/assets/csv/(enter icp here).csv

Once this CSV is available online, import into Excel is simple. However it appears this is only generated once the ICP has been run through form at least once.

2 - API

The API appears cool, but I have no experience with these data sources and have has little luck online. Do you know if I can get Excel to query API and return data?

Maybe there is another option that I am not aware of that might work also?

Thanks a lot,

Nathan

Order by: Standard | Newest | Votes
energie posted this 11 June 2016

Okay, so with a fair bit of googling I have figured out working VBA code. It provides one long string of data, but it's a start.

Here's the code if this will help anyone else. The ICP number is a named range in Excel and replace "KEY" with your individual authorisation token.

==

Dim result As String
Dim myURL As String
Dim myICP As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myICP = Range("icpnumber").Value

myURL = "https://emi.azure-api.net/ICPConnectionData/single/?id=" & myICP
winHttpReq.Open "GET", myURL, False
winHttpReq.setRequestHeader "Ocp-Apim-Subscription-Key", "KEY"
winHttpReq.send
result = winHttpReq.responseText

 

 

 

 

msouness posted this 15 June 2016

I've put together a spreadsheet that works in Excel for Windows that downloads one ICP's details and populates cells with the following parameters with good consistency:

ICP, Unit, Number, Street, Suburb, Town, Region, Post Code, Network, GXP, Reconciliation Type, Generation, Fuel, Network Price Category, Loss Factor, Trader, MEP, Profile, AMI flag.

Simply call the function "=getICPdetail(   cell with icp in it   )"

This places the string in a cell.

There is also another macro that coverts text to columns (I didn't get JSON parseing functioning).

Drop me a note and I'll make the sheet available.

I've also put together a function that accepts street addresses and returns ICP's if that is of interest...

Also have some macros that work on excel for Mac.

Regards, Malcolm

-------------GETICPDETAIL FUNCTION FOR WINDOWS EXCEL------------------

Public Function getICPdetail(ICP As String) As Variant
    Dim xmlhttp As Object
    Dim strURL As String
    Dim CurrentYear As String
    Dim x As String
    Dim key As String    
    On Error GoTo FuncFail
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    strURL = "https://emi.azure-api.net/ICPConnectionData/?id="
    key = "the API key goes here"
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
    With xmlhttp
        .Open "get", strURL & ICP, False, key
        .setRequestHeader "Ocp-Apim-Subscription-Key: ", key
        .send
        x = .ResponseText
    End With
    getICPdetail = x
    Set xmlhttp = Nothing
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Exit Function
    
FuncFail:
    getICPdetail = CVErr(xlErrNA)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

----------------------------------------------------------

energie posted this 15 June 2016

Hi Malcolm,

Thanks, I went down the text to columns + lots of formatting route for a while, but then ended up getting JSON parsing working.  I couldn't get VBA-JSON package working, but found a 2-step parser for the JSON string which worked well (http://www.codeproject.com/Articles/828911/Recursive-VBA-JSON-Parser-for-Excel). It formats JSON nicely and puts in a txt file, then second step is to read the txt file back into Excel and strip out data at the same time.

Would be interested in street address -> ICP code if possible. Am using the 'My Meter' interface at present. I can't find a private message functionality on here, but you can email me: nathan@energie.nz

Cheers

Nathan

msouness posted this 16 June 2016

Here's my getICP(number, street, town, region) function for Windows Excel.

Public Function getICP(unitOrNumber As String, streetOrPropertyName As String, suburbOrTown As String, region As String) As Variant
    Dim xmlhttp As Object
    Dim strURL As String
    Dim x As String
    Dim key As String
Dim request As String
    request = "unitOrNumber=" & unitOrNumber & "&streetOrPropertyName=" & streetOrPropertyName & "&suburbOrTown=" & suburbOrTown & "&region=" & region
    'request = "unitOrNumber=5&streetOrPropertyName=Hakeke&suburbOrTown=&region="
 
    On Error GoTo FuncFail
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    strURL = "https://emi.azure-api.net/ICPConnectionData/?" & request
    key = "  your API key goes here"
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
    With xmlhttp
        .Open "get", strURL, False, key
        .setRequestHeader "Ocp-Apim-Subscription-Key: ", key
        .send
        x = .ResponseText
    End With    
    getICP = x
    Set xmlhttp = Nothing
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Exit Function
FuncFail:
    getICPdetail = CVErr(xlErrNA)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

Matthew Keir posted this 01 July 2016

Attached is a zip file containg a simple VBA example that extracts information for a single ICP into excel.

Attached files

  • Liked by
  • SimonT
energie posted this 27 July 2016

Hi all,

Is there a way to tell from ICP information whether site is 1 / 2 / 3-phase?

Thanks,

Nathan

Ron Beatty posted this 28 July 2016

The registery does not record the phase configuration of points of connection or metering components. In a few cases the Distributor Price Category code may indicate if an installation is 1/2/3 phase.

hungodd94 posted this 05 April 2018

thank you souness for showing the guide to export from ICP to excel 

mitchellm posted this 28 August 2018

Hi

I'm neither an engineer nor a programmer. The VBA code for the electricity registry is very helpful, thanks for posting it. I know this is an EA forum but would you be able to ask your colleagues at Jade if there is a gas registry equivalent?

Merv

Nicole Gagnon posted this 06 September 2018

I'm not aware of VBA code for the gas registry. It would be best to ask them directly - info@gasindustry.co.nz  

SimonT posted this 11 September 2018

Many thanks for the info in this thread - it has been very helpful

funcsol posted this 26 October 2018

Hi all, 

Based on the ICP API, I've done a simple get & format tool in this sheet. 

https://drive.google.com/file/d/13KC4XToeHdfqI65ZRTh6GK-aKInq5qZd/view 

Since the inspiration came from this thread, I thought it fit to pay it forward. I chose Excel as the tool since every company I might work for will allow Excel ;) 

Its still a work in progress, & if one was to use it, macros & content will need to be enabled.

The Response JSON is formated in Power Query, everything else is pretty self evident. 

If there are obvious errors, let me know ;) 

  • Liked by
  • Matthew Keir
  • msouness
Close