R

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://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://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://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'")