DATA SERVICE

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: https://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=***** 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:

1. open a blank Excel worksheet
2. 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
3. that’s it – the data should appear in the spreadsheet

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.

Running reports

Sample code below loads data from a 5min price report and plots it. Note that this method runs a NEOpoint report rather than an actual query.

w=read.csv(file="https://www.neopoint.com.au/Service/Csv?f=101+Prices%5CDispatch+and+Predispatch+Prices+5min&from=2018-11-05&period=Daily&instances=§ion=-1&key=*****")
plot(w$DateTime, w$NSW1.Price)
lines(w$DateTime, w$NSW1.Price)
rdate<-strptime(w$DateTime, "%Y-%m-%d %H:%M:%S" )
                            plot(rdate, w$NSW1.Price)
                            lines(rdate, w$NSW1.Price)

Running SQL queries

To run SQL queries you need a QueryTool enabled account. Because queries tend to be quite large the HTTP GET method used above cannot be used due to the ~2K limit on the length of a URL, therefore the POST method must be used. The sample code below shows how execute a query. It uses the httr library

library(httr)

query <- "select `SETTLEMENTDATE`, `RUNNO`, `REGIONID`, `PERIODID`, `RRP`, `EEP`,
`INVALIDFLAG`, `LASTCHANGED`, `ROP`, `RAISE6SECRRP`, `RAISE6SECROP`,
`RAISE60SECRRP`, `RAISE60SECROP`, `RAISE5MINRRP`, `RAISE5MINROP`,
`RAISEREGRRP`, `RAISEREGROP`, `LOWER6SECRRP`, `LOWER6SECROP`, `LOWER60SECRRP`,
`LOWER60SECROP`, `LOWER5MINRRP`, `LOWER5MINROP`, `LOWERREGRRP`, `LOWERREGROP`,
`PRICE_STATUS` from mms.tradingprice where SETTLEMENTDATE between '2018-11-1' and '2018-11-2' limit 0, 10000"
         requ <- list(
  query = query,
  key = "*****",
  format="csv"
)
res <- POST("https://www.neopoint.com.au/data/query", body = requ, encode = "form", verbose())
tabtext <- content(res, "text")
tab <- read.csv(text = tabtext, header = TRUE)

The above might look a bit tedious but it is easy to create a function that will wrap all of that up so that running a query just becomes a single function call. In the code sample below we define a function to run a query then we call it. The function takes a query string and returns a data frame.

RunQuery <- function(query) {
            requ <- list(
	  query = query,
	  key = "*****",
	  format="csv"
	)
	res <- POST("https://www.neopoint.com.au/data/query", body = requ, encode = "form", verbose())
	tab <- content(res, "text")
	read.csv(text = tab, header = TRUE)
}

RunQuery("select * from mms.tradingprice where SETTLEMENTDATE between '2018-11-2' and '2018-11-3'")

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("https://www.neopoint.com.au/Service/Csv?f=101+Prices%5CDispatch+Prices+5min&from=2017-12-10&period=Daily&instances=§ion=-1&key=xxxx")