Data services for your favourite tools. Load multiple data sets fast.

Use NEO report data in all of your favourite tools like Excel, R, Matlab or Python, e.g. in Excel you can now run and load data from multiple reports with just the click of a button. Its easy to do and a lot easier than manually copying and pasting data, especially for repetitive analyses.

General

Both NEOpoint and NEO provide data services so you can easily load data directly into your favourite third party tool such as Excel, R, Matlab, Python. All these tools offer a single command “web query” which takes a URL. Each tool expects a different data format, either XML, JSON or CSV so NEOpoint and NEO support all these formats. The main difference between data services from NEO and NEOpoint is as follows:

  • NEO data services have a localhost URL and are only accessible on the PC that is running NEO, whereas NEOpoint data services are acceptable from any machine if running on an intranet or from anywhere in the world if running on an internet accessible server.
  • NEO uses report paths whereas NEOpoint uses Favourite paths. The following sections describe how to access NEOpoint and NEO data services from various tools.

An example of two equivalent URL paths is shown below:

  • NEOpoint: http://www.nempoint.com/Service/Csv?f=101+Prices%5cRegion+Prices+5min&period=Daily&from=2015-12-21&instances=NSW1§ion=0&key=ieXsysXXXX
  • NEO: http://localhost:8007/xml?report=Australia%5cMMS+R6%5cDispatch%5c1+Price%2c+Demand%2c+Flows%5c1+ EnergyPrice%5cRegion+Price+5min&instances=NSW1&pc=0d1d
Getting the service URL is easy. In NEO right click on any chart and select "Copy Service URL". In NEOpoint click on the Links toolbar button and you get a drop down list of links for each type of service as shown below.

Excel

As with all the other tools data services allows you to load multiple data sets automatically. We have an XLSM file that demonstrates a simple case of loading 5 min price and demand from two separate reports. The macro is easy to modify to load any report, time period or instances. Excel sample

Excel expects web query data to be in XML format. You can either load it manually, or if this is something you do on a regular basis you may want to create a macro. The advantages of macros is that you can instantly load any number of live data sets and perform calculations on them all with the press of a button.

Manual loading:

To start, with open NEOpoint and run the required report and then click the “Links” button, select and copy XML link. Sample XML link: http://www.neopoint.com.au/Service/Xml?f=104+Bids%5cRegion+Merit+Order+Stack+5min&period=Daily& from=2015-10-13&instances=Generator;NSW1§ion=0&key=as234dkj XML link has several parameters that you can customize.

  • F – favourites object + selected report name
  • Period – report period code
  • From – report from date
  • Instances – report instances
  • Section – depends on the report but in most cases can be left as 0
  • Key – NEOpoint login account API key

To manually load a result follow these steps:

  • open a blank Excel worksheet
  • click on the Data tab and click on “From Web”, in the dialog Address paste the URL you copied from NEOpoint, click GO then Import, set the location where you want the data to go
  • that’s it – the data should appear in the spreadsheet

The big advantage of data services is automatically loading results. This makes it easy to create spreadsheets that with the click of a button will load several data sets and perform calculations. The macro text below shows how to load data automatically. You can simply copy the text for the macro and paste into your own copy of an Excel spreadsheet, modifying the URL as required. You can also set where you want the data to be loaded and load additional data sets in the same macro by repeating the XmlImport line with the required URL for each additional data set. To understand the macro we also need to explain two other lines of code:

  • Every time you import XML data, Excel creates an “XMLMap” so in your macro you need to add some code to delete ALL the XMLMaps otherwise they will keep accumulating. That’s what the XmlMap.Delet line does.
  • When data is imported it is marked as read only and hence you can’t load new data on top of it hence we add a “ClearContents” command to clear the contents of the cells you are about to import data into. You will need to adjust the range “G:H” to match the columns where you will be importing data.

The sample macro code is shown below.

Sub Macro1()
'
' Get prices
'
For Each XmlMap In ActiveWorkbook.XmlMaps
XmlMap.Delete
Next
Columns("G:H").ClearContents
ActiveWorkbook.XmlImport URL:= _
"http://www.nempoint.com/Service/Xml?f=101+Prices\Dispatch+Prices+5min&period=Daily&from=2015-10-13&instances=§ion=0&key=iesys123" _
, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$G$1")
End Sub

R Language

R is a very popular open source language based on the S language and used for statistical data analysis. The simplest way to load data is via the CSV format. Sample code below loads 5min price and plots it.

w=read.csv(file="http://www.nempoint.com/Service/Csv?f=101+Prices%5cRegion+Prices+5min&period=Daily&from=2015-05-03&instances=NSW1§ion=-1&key=abc123def")
plot(w$DateTime, w$NSW1.Price.5min)
lines(w$DateTime, w$NSW1.Price.5min)
rdate<-strptime(w$DateTime, "%Y-%m-%d %H:%M:%S" )
                            plot(rdate, w$NSW1.Price.5min)
                            lines(rdate, w$NSW1.Price.5min)

Python

Python was probably the best of the tools we reviewed! Best of all its free and very widely supported with a large number of libraries like numpy, SciPy, Matplotlib, Pandas One of the best Python IDEs is PTVS from Microsoft with the Visual Studio Community Edition (all free). See all the details here: https://pytools.codeplex.com/wikipage?title=PTVS%20Installation . The screen shot below shows the PTVS user interface with some sample code for reading a NEOpoint data service.

The Python source code for loading some data is shown below:

import urllib2
import csv
import dateutil
import urllib2
import pyparsing
import matplotlib.pyplot as plt
import datetime as dt
import dateutil.parser as parser
url = 'http://nempoint.com/service/csv?key=abc123def§ion=0&f=101+Prices%5cDispatch+Prices+5min&period=Daily&from=2015-04-09&instances='
f = urllib2.urlopen(url)
cr = csv.DictReader(f)
dat = []
price = []
for row in cr:
    dat.append(parser.parse(row['DateTime']))
    price.append(float(row['Price_5min']))
fig = plt.figure()
plt.plot(dat, price)
plt.show()

The chart resulting from running that Python code is shown below.

Google Sheets

GS can read data in XML or CSV format but we recommend CSV format. The screen shot below shows reading data in by pasting a command like: =ImportData("http://www.nempoint.com/Service/Csv?f=101+Prices%5CDispatch+Prices+5min&from=2017-12-10&period=Daily&instances=§ion=-1&key=xxxx")

Alternatively if you want to read the data using a script you can use the sample below as a guide.

                    function myFunction() {
  var csvUrl = "http://www.nempoint.com/Service/Csv?f=101+Prices%5CDispatch+Prices+5min&from=2017-12-10&period=Daily&instances=§ion=-1&key=xxxx";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(4,4, csvData.length, csvData[0].length).setValues(csvData);
}