Logic Machine Forum
Get prices from webpage via Excel - Printable Version

+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: Get prices from webpage via Excel (/showthread.php?tid=1370)

Get prices from webpage via Excel - Rune - 02.05.2018

Maybe this is not possible, but it is worth a question.

Nordpool got this webpage that publish tomorrows energy prices. This information will be important when the energy prices will be dynamic and follow these prices in our country from 2019. A combination of this price information and maybe treshold values will be good to have when trying to create a load control for controlling the energy consumption.

My problem is that this information has to be converted to KNX. I can see that it is possible to get this information by downloading an Excel sheet.

https://www.nordpoolgroup.com/Market-data1/Dayahead/Area-Prices/NO/Hourly/?view=table

Is it possible to create a script that download this Excel sheet everyday?

And if so, this information should be sent by KNX once an hour as it is in the table. For example, 12:00 the price for 12-13 is sent, 13:00 the price for 13-14 is sent.

Is all this possible or it just a dream from my side? Smile

RE: Get prices from webpage via Excel - Erwin van der Zwart - 02.05.2018

Hi,

The only thing you can do is something like this:

Code:
https = require("ssl.https")
data, code = https.request('https://www.nordpoolgroup.com/Market-data1/Dayahead/Area-Prices/NO/Hourly/?view=table')
log(data)

But i think the data table is not fetched like this, you will need to use there API but that is a paid version (members only)

BR,

Erwin

RE: Get prices from webpage via Excel - admin - 03.05.2018

You will get a table with 30 items as the result (24 hour value + min/max/etc).
You can change the location by modifying cols table index (1 = Oslo, 2 = Kr.sand, 3 = Bergen, 4 = Tr.heim, 5 = Molde, 6 = Tromsø).

Code:
require('json')
require('ssl.https')

res = ssl.https.request('https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK')
values = {}

if res then
  res = json.pdecode(res)

  if type(res) == 'table' then
    for _, row in ipairs(res.data.Rows) do
      cols = row.Columns
      value = cols[ 1 ].Value:gsub(',', '.')
      values[ #values + 1 ] = tonumber(value)
    end
  end
end

log(values)


RE: Get prices from webpage via Excel - Rune - 05.05.2018

Thanks a lot so far!

I have now the information in my log, but is it possible to tell the LM5 to write each value once an hour? For example value [9] will be written 8:00?


* table:
[1]
* number: 265.92
[2]
* number: 208.87
[3]
* number: 230.49
[4]
* number: 228.37
[5]
* number: 208.2
[6]
* number: 173.16
[7]
* number: 154.43
[8]
* number: 167.17
[9]
* number: 203.66
[10]
* number: 257.52
[11]
* number: 250.76
[12]
* number: 205.59
[13]
* number: 201.25
[14]
* number: 176.25
[15]
* number: 150.57
[16]
* number: 136.67
[17]
* number: 201.05
[18]
* number: 244.1
[19]
* number: 293.62
[20]
* number: 319.1
[21]
* number: 323.63
[22]
* number: 322.86
[23]
* number: 294
[24]
* number: 277.88

RE: Get prices from webpage via Excel - Jørn - 05.05.2018

Code:
now = os.date('*t')
pickhour = (now.hour + 1)

require('json')
require('ssl.https')

res = ssl.https.request('https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK')
values = {}

if res then
 res = json.pdecode(res)

 if type(res) == 'table' then
   for _, row in ipairs(res.data.Rows) do
     cols = row.Columns
     value = cols[ 1 ].Value:gsub(',', '.')
     values[ #values + 1 ] = tonumber(value)
   end
 end
end

log(values[pickhour])
This seems to work, run it as a scheduled script 1 minute over every hour.
But with the current link in there, it looks like it's getting prices for the next day`?

RE: Get prices from webpage via Excel - Rune - 05.05.2018

(05.05.2018, 18:35)Jørn Wrote: This seems to work, run it as a scheduled script 1 minute over every hour.
But with the current link in there, it looks like it's getting prices for the next day`?

Thanks, will try that out.

Yes, that is correct and that is the challenge. The time price has to be either stored or something and I want the LM5 to write the address for the actual hour.

For example, we collect tomorrow prices today and tomorrow the LM5 has to write "201,25" 12:00 to a group address at the KNX bus.

RE: Get prices from webpage via Excel - admin - 07.05.2018

This will get day-ahead prices from yesterday:

Code:
date = os.date('*t')
date.day = date.day - 1
param = os.date('%d-%m-%Y', os.time(date))

res = ssl.https.request('https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK&endDate=' .. param)


RE: Get prices from webpage via Excel - Jørn - 07.05.2018

Code:
require('json')
require('ssl.https')

now = os.date('*t')
pickhour = now.hour + 1
date = os.date('*t')
date.day = date.day
param = os.date('%d-%m-%Y', os.time(date))

res = ssl.https.request('https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK&endDate=' .. param)
values = {}

if res then
 res = json.pdecode(res)

 if type(res) == 'table' then
   for _, row in ipairs(res.data.Rows) do
     cols = row.Columns
     value = cols[ 1 ].Value:gsub(',', '.')
     values[ #values + 1 ] = tonumber(value)
   end
 end
end

grp.write('1/1/1', values[pickhour]) -- output address

Then this works great Wink
I had to remove the " - 1" from admins code, it led to prices from yesterday.

Mvh Wink

RE: Get prices from webpage via Excel - admin - 07.05.2018

In this case you can just do:
Code:
param = os.date('%d-%m-%Y')
res = ssl.https.request('https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK&endDate=' .. param)


RE: Get prices from webpage via Excel - Jørn - 09.05.2018

Allright, cleaned that up and added some other (i think) useful functions  (:
Also put all configurable parameters easily available at the top of the script.


Code:
output = '1/1/1' -- output group address, 14 byte
city = 1    -- 1 = Oslo, 2 = Kr.sand, 3 = Bergen, 4 = Tr.heim, 5 = Molde, 6 = Tromsø
timeshift = 1    -- 0= Last hour, 1 = current hour, 2 = next hour
scale = 1    -- 1 = MWh price, 1000 = KWh price.


-- Script for writing hourly power prices from nordpool to knx object.
require('json')
require('ssl.https')

now = os.date('*t')
pickhour = now.hour + timeshift
param = os.date('%d-%m-%Y', os.time(date))

res = ssl.https.request('https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK&endDate=' .. param)
values = {}

if res then
 res = json.pdecode(res)

 if type(res) == 'table' then
   for _, row in ipairs(res.data.Rows) do
     cols = row.Columns
     value = cols[ city ].Value:gsub(',', '.')
     values[ #values + 1 ] = tonumber(value)
   end
 end
end

grp.write(output, values[pickhour] / scale)


RE: Get prices from webpage via Excel - Rune - 09.05.2018

Tried your last script Jørn and it works fine! I also got a script from a friend and it works in the same way as yours, but built different.

Anyway, attached a picture of my logging from today. Nordpool's prices for Bergen in øre (100 øre = 1 krone = 0,1€) and my consumption in Watts. Smile

Will also try your new script. I think this will be very important from 2019 in Norway.

Thank you so much. Smile

RE: Get prices from webpage via Excel - Rune - 20.06.2018

Both my scripts stopped working 26.05.2018.

Can anyone see what is wrong? Think I tried to fix it, but I messed around with so many different versions of the scripts that I lost track.

require('json')
require('ssl.https')

now = os.date('*t')
pickhour = now.hour + 1
date = os.date('*t')
date.day = date.day
param = os.date('%d-%m-%Y', os.time(date))

res = ssl.https.request('https://www.nordpoolgroup.com/Market-data1/Dayahead/Area-Prices/NO/Hourly/?dd=NO5&view=table' .. param)
values = {}

if res then
res = json.pdecode(res)

if type(res) == 'table' then
for _, row in ipairs(res.data.Rows) do
cols = row.Columns
value = cols[ 1 ].Value:gsub(',', '.')
values[ #values + 1 ] = tonumber(value)
end
end
end

grp.write('4/4/41', values[pickhour]) -- output address

RE: Get prices from webpage via Excel - admin - 21.06.2018

Your URL is incorrect, it loads HTML page instead of raw JSON. It should look like this:
https://www.nordpoolgroup.com/api/marketdata/page/23?currency=NOK

RE: Get prices from webpage via Excel - Rune - 12.09.2018

Quote from Nordpool:

"Automatic extraction of data from this website and/or use for commercial purposes is strictly prohibited under Nord Pool’s terms & conditions. For more information regarding data usage, contact pds@nordpoolgroup.com."

So, no point in trying to fix this script anyway.

RE: Get prices from webpage via Excel - Mr.D - 03.03.2019

Hi,

I haven't tried any of the scripts mentioned in these posts, but I can see that there might be some "legal" issues causing the script not to work.
Have you found a workaround? As mentioned in the post this is becoming more and more important here in Norway.
For once this would become very handy for scheduling charing of my car, also for trying to avoid peaks when it comes to heating cables etc.

RE: Get prices from webpage via Excel - sisenis - 04.04.2021

Please check here: https://www.hackster.io/AndrewLinden/iot2020-heating-controller-with-nps-support-95dcde
"massive json object from this URL: http://www.nordpoolspot.com/api/marketdata/page/10. By default you get latest data available. To get data for specific date one can add a get parameter endDate. For example endDate=30-03-2017"