Script to track Day Ahead prices for electricity on the ENTSOE-E Platform

[tigi]

This script will fetch the daily prices from the ENTSO-E Transparency platform for your country.

It will also give the average price of the day, percentages based on the lowest and highest price for all prices of the day and the group address of the lowest and highest price object, so the lowest price is 0% while the highest price is 100%. A value of 34% means you will pay 34% more than what is possible on another time of the day.

Apart from this daily data, you can do a trend log on the average price, which can then be processed monthly to get a monthly average, along with your consumption tracking you are able to calculate a more precise price per kWh, certainly if your country makes use of some sort of 'capacity tariff'.

All useable values e.g. prices, percentages, objects group addresses of lowest and highest price and average, are stored in virtual objects for use in other scripts.

For prices, 24 objects are needed, for the percentages 24 objects are needed, and 3 objects for lowest, highest and average



Visual of objects



   



Prerequisites

• You must register a free account on the ENTSO-E Transparency Platform and request a personal API key
  
  
• Scheduled Script at 0 minute, 14 hours, daily, every day, every month
  
  
• The way (virtual) objects for price and percentage are numbered gives you the advantage to see the hours based on the group address, see further
  
  
• The first object for storing the prices must start with 0 (eg.: 34/1/0 or 32/3/0,...)
  So object 32/1/4 represents the price for hour 04:00, 32/1/20 the price for hour 20:00
  Prices are in €/MWh
  14.4 byte floating point
  
  
• The first object for storing the percentages must start with 0 (eg.: 34/1/0 or 32/3/0,...)
  So object 33/1/4 represents the percentage for hour 04:00, 33/1/20 the percentage for hour 20:00
  14.4 byte floating point
  
  
• The objects for lowest, highest and average price are 14.4 byte floating point
  Prices are in €/MWh
  
  
• The object for lowest and highest groupaddress must be 16.14 byte ASCII string
  

Variables

• fetchfortommorow: default TRUE, normally prices are fetched for tommorow making it more useable, but if you want to fetch prices for today set this to FALSE
  
• SecurityTokenAPI: your personal API security token that you can request by making a free account on the ENTSOE-E Transparency Platform and by sending an email to them.
  
• EntsoeDomain: the string to be used for your country, you can find it here in this API manual
  
• StartGrpAddr: The 2 first numbers of the objects to be used for prices including seperation symbol, eg: 34/2 or 32/1
  
• StartGrpAddrPercent: The 2 first numbers of the objects to be used for percentages including seperation symbol, eg: 34/2 or 32/1
  
• SecondsPastTillRefetch: Safety swith to prevent to much polling if cron job is misconfigured (prevents you from being blocked on the platform) Default is 39600 seconds, being 11 hours
  
• StartTimeHours, StartTimeMinutes, StopTimeHours, StopTimeMinutes: Prevents polling data when it's not possible. Since prices for the day ahead are published at 13:00 hours the current day, it's no use to run this script outside certain hours.
  
• objaddrCheckUpdateTime: Object used to check the last update time, default the second price object being x/1/1
  
• objaddrLowestprice: Object used to store lowest price
  
• objaddrHighestprice: Object used to store highest price
  
• PrintLOG: Default FALSE, set TRUE for troubleshooting
  

Code:

local fetchfortommorow = true -- true for tommorow, false for today
local SecurityTokenAPI = 'your-personal-key' -- your personal security token, get it at entsoe transparency platform
local EntsoeDomain = '10YBE----------2' -- find your domain for the country you are living at https://transparency.entsoe.eu/content/static_content/Static%20content/web%20api/Guide.html#_load_domain
local StartGrpAddr = '34/1' -- group addresses used to store the €/MWh prices per hour, x/x/0 is 00:00 hour, x/x/1 is 01:00 hour
local StartGrpAddrPercent = '34/2' -- group addresses to store calculated pecentages for the hours, x/x/0 is 00:00 hour
local SecondsPastTillRefetch =  39600 -- seconds that must be past till new refetch will be done, default 39600 seconds, is 11 hours
local StartTimeHours=13 -- start time in which fetching is allowed, used in function IsTimeBetween
local StartTimeMinutes=00 -- start minutes in which fetching is allowed, used in function IsTimeBetween
local StopTimeHours=23 -- stop time in which fetching is allowed, used in function IsTimeBetween
local StopTimeMinutes=59 -- stop minutes in which fetching is allowed, used in function IsTimeBetween
local objaddrCheckUpdateTime = '34/1/1' -- object to check last updated date from
local objaddrLowestprice = '34/1/26' -- object to store groupaddress of lowest price of the day
local objaddrHighestprice = '34/1/27' -- object to store groupaddress of highest price of the day
local PrintLOG = false -- logging

  -- https://stackoverflow.com/questions/35402992/how-to-check-if-current-time-is-between-two-others
  -- Function to check if current time is between 2 other defined times
  -- Returns true if time is between 2 other times
  local function getMinutes(hours, minutes)
      return (hours*60)+minutes
  end

  local function IsTimeBetween(StartH, StartM, StopH, StopM, TestH, TestM)
      if (StopH < StartH) then -- add 24 hours if endhours < starthours
          local StopHOrg=StopH
          StopH = StopH + 24
          if (TestH <= StopHOrg) then -- if endhours has increased the currenthour should also increase
              TestH = TestH + 24
          end
      end

      local StartTVal = getMinutes(StartH, StartM)
      local StopTVal = getMinutes(StopH, StopM)
      local curTVal = getMinutes(TestH, TestM)
      return (curTVal >= StartTVal and curTVal <= StopTVal)
  end   

  local function IsNowBetween(StartH,StartM,StopH,StopM)
    local time = os.date("*t")
      if PrintLOG then
    log(time.hour, time.min) --check if time is correct, if not change to os.date("!*t") for UTC time
    end
    return IsTimeBetween(StartH, StartM, StopH, StopM, time.hour, time.min)
  end
    if PrintLOG then
      log(IsNowBetween(StartTimeHours, StartTimeMinutes, StopTimeHours, StopTimeMinutes))
  end
  -- https://stackoverflow.com/questions/35402992/how-to-check-if-current-time-is-between-two-others

objectupdatedate = (grp.find(objaddrCheckUpdateTime).updatetime) -- get update date for chosen object
diff = os.difftime(os.time(),objectupdatedate) -- difference between current time and objectupdatetime in seconds
if diff > SecondsPastTillRefetch then --check if last run was more than x seconds ago https://www.google.com/search?q=seconds+to+hours
      if PrintLOG then
          log('more than '..SecondsPastTillRefetch..' seconds since last run')
    end
    if (IsNowBetween(StartTimeHours, StartTimeMinutes, StopTimeHours, StopTimeMinutes)) then --check if current time is between hours that data can be fetched
        if PrintLOG then    
            log('data can be fetched')
      end
     
      -- https://forum.logicmachine.net/showthread.php?tid=4268&highlight=entsoe
      if fetchfortommorow then
        date = os.date('%Y%m%d', os.time() + 86400) -- date is tommorow, default setting
      else
        date = os.date('%Y%m%d') --date is today
      end

      periodstart = date .. '0000'
      periodend = date .. '2300'
      log(date)

      url = 'https://web-api.tp.entsoe.eu/api?securityToken='..SecurityTokenAPI..'&' ..
        'documentType=A44&in_Domain='..EntsoeDomain..'&out_Domain='..EntsoeDomain..
        '&periodStart=' .. periodstart .. '&periodEnd=' .. periodend

      data, code = require('socket.http').request(url)
      if not data or code ~= 200 then
        log('request failed', data, code)
        return
      end

      prices = {}
      key = 0

      callbacks = {
        StartElement = function(p, tag)
          if tag == 'TimeSeries' then
            key = key + 1
            prices[ key ] = {}
          elseif tag == 'position' then
            state = 'position'
          elseif tag == 'price.amount' then
            state = 'price'
          end
        end,
        EndElement = function(p, tag)
          state = nil
        end,
        CharacterData = function(p, value)
          value = value:trim()

          if #value > 0 then
            if state == 'position' then
              position = tonumber(value)
            elseif state == 'price' then
              prices[ key ][ position ] = tonumber(value)
            end
          end
        end
      }

      parser = require('lxp').new(callbacks)
      parser:parse(data)
      parser:close()

      if key > 0 then
        for i, price in pairs(prices[ 1 ]) do
          j = i - 1 -- shifts the first key from 1 to 0, so the first groupaddress is x/x/0 for 00:00 hour, next is x/x/1 for 01:00 hour,etc...
            grp.checkupdate(StartGrpAddr..'/' .. j, price) -- update objects with newest prices
          --log('uur ' ..j..' prijs: ' ..price)
        end
             
          -- https://stackoverflow.com/questions/69359710/how-to-get-the-smallest-number-in-a-table-with-lua
               lowestprice = math.min(unpack(prices[ 1 ])) --lowest number in table
                 highestprice = math.max(unpack(prices[ 1 ])) --highest number in table
     
            -- https://stackoverflow.com/questions/25835591/how-to-calculate-percentage-between-the-range-of-two-values-a-third-value-is
                --input = grp.getvalue('34/1/13')
          for i, price in pairs(prices[ 1 ]) do
         j = i - 1
             percentage = ((price - lowestprice) * 100) / (highestprice - lowestprice)
             --log('at hour '..j..':00 price is '..math.floor(percentage)..'% higher than lowest price today')
        grp.checkupdate(StartGrpAddrPercent..'/' .. j, percentage) --update objects with newest percentages
       
          if lowestprice == price then
              grp.checkupdate(objaddrLowestprice, StartGrpAddr..'/' .. j)
          end
          if highestprice == price then
              grp.checkupdate(objaddrHighestprice, StartGrpAddr..'/' .. j)
          end
        end
     
          -- https://www.codegrepper.com/code-examples/lua/lua+calculate+average+number
          function math.average(t)
            local sum = 0
                for _,v in pairs(t) do -- Get the sum of all numbers in t
              sum = sum + v
            end
            return sum / #t
          end
        grp.checkupdate('34/1/25', math.average((prices[ 1 ])))
        end
     -- https://forum.logicmachine.net/showthread.php?tid=4268&highlight=entsoe
     else
        if PrintLOG then
            log('data cannot be fetched yet')
      end
      end 
else
  if PrintLOG then
      log('data has already been fetched and updated, not necessary to re-fetch data')
  end
end

Thanks and gratitude to all the people that inspired me and where I used code from!
Feel free to optimise and extend!

[Rookie]

I have need to schedule some consumption to the cheapest hours on night time, examples
1. "four cheapest hours in the row" or 
2. "four cheapest hours in the night time"

What is the simple and robust way to handle this kind of scheduling? Anyone done this kind of solution based on this ENTSOE-E script?

[admin]

1. prices must be a table where key is hour (0..23) and value is price. The resulting minhour variable is the first hour of the 4 hour period with the lowest prices.

Code:

minsum = math.huge
minhour = 0

step = 4

for hour = 0, 24 - step do
  sum = 0

  for i = hour, hour + step - 1 do
    sum = sum + prices[ i ]
  end

  if sum < minsum then
    minsum = sum
    minhour = hour
  end
end

log(minhour)

2. For night prices use this, adjust night hours as needed (22 to 5 inclusive in this example).

Code:

for hour = 0, 23 do
  if hour >= 22 or hour <= 5 then
    nightprices[ #nightprices + 1 ] = {
      hour = hour,
      price = prices[ hour ]
    }
  end
end

table.sort(nightprices, function(a, b)
  return a.price < b.price
end)

for i = 1, 4 do
  item = nightprices[ i ]
  log(item.hour, item.price)
end

[fleeceable]

Good work @tigi! 

I usually add automatic group address creation on this kind of programs so when you copy code to a new LM, you can run this program within seconds.
Also edited line 151 (group address was hard coded) and removed Average, Min and Max price Group addresses because these gonna generated automatically...
Now you need basically edit only three group address:  StartGrpAddr,  StartGrpAddrPercent and objaddrCheckUpdateTime + activate "create_addresses" on first time when running the code.

I have some ideas to create controlling part (solar inverters and battery charging/discharging) on top of this code but let's see when I have time to do that. Gonna share the results then...
 

Code:

local fetchfortommorow = true -- true for tommorow, false for today
local SecurityTokenAPI = 'your-api-key-in-here' -- your personal security token, get it at entsoe transparency platform
local EntsoeDomain = '10Y1001A1001A39I' -- find your domain for the country you are living at https://transparency.entsoe.eu/content/static_content/Static%20content/web%20api/Guide.html#_load_domain
local StartGrpAddr = '60/1' -- group addresses used to store the €/MWh prices per hour, x/x/0 is 00:00 hour, x/x/1 is 01:00 hour
local StartGrpAddrPercent = '60/2' -- group addresses to store calculated pecentages for the hours, x/x/0 is 00:00 hour
local SecondsPastTillRefetch =  39600 -- seconds that must be past till new refetch will be done, default 39600 seconds, is 11 hours
local StartTimeHours=13 -- start time in which fetching is allowed, used in function IsTimeBetween
local StartTimeMinutes=00 -- start minutes in which fetching is allowed, used in function IsTimeBetween
local StopTimeHours=23 -- stop time in which fetching is allowed, used in function IsTimeBetween
local StopTimeMinutes=59 -- stop minutes in which fetching is allowed, used in function IsTimeBetween
local objaddrCheckUpdateTime = '60/1/0' -- object to check last updated date from
--local objaddrAverageprice = '60/1/25' -- object to store average value
--local objaddrLowestprice = '60/1/26' -- object to store groupaddress of lowest price of the day
--local objaddrHighestprice = '60/1/27' -- object to store groupaddress of highest price of the day
local PrintLOG = false -- logging
local create_addresses = false

--**********CREATE GROUP ADDRESSES******************
function createga(groupaddress,datatype2,name2)
  exist = grp.alias(groupaddress)
  if exist == nil then
    address = grp.create({
    datatype = datatype2,
    address = groupaddress,
    name = name2,
    comment = 'Automatically created object',
    units = '',
    tags = { },
    })
  end
end

if create_addresses then
  for i= 0, 27, 1 do
    if i < 24 then
      createga(StartGrpAddr ..'/'..i,dt.float32,'Electric - Entsoe day ahead  ' .. i)
      createga(StartGrpAddrPercent ..'/'..i,dt.uint16,'Electric - Entsoe day ahead  ' .. i .. ' Percent')
    elseif    i==25 then
      createga(StartGrpAddr ..'/'..i,dt.float32,'Electric - Entsoe day ahead Average')
    elseif i == 26 then
      createga(StartGrpAddr ..'/'..i,dt.string,'Electric - Entsoe day ahead Lowest GrpAddr') 
    elseif i == 27 then
      createga(StartGrpAddr ..'/'..i,dt.string,'Electric - Entsoe day ahead Highest GrpAddr')
    end
  end
  os.sleep(2)
end

  -- https://stackoverflow.com/questions/35402992/how-to-check-if-current-time-is-between-two-others
  -- Function to check if current time is between 2 other defined times
  -- Returns true if time is between 2 other times
  local function getMinutes(hours, minutes)
      return (hours*60)+minutes
  end

  local function IsTimeBetween(StartH, StartM, StopH, StopM, TestH, TestM)
      if (StopH < StartH) then -- add 24 hours if endhours < starthours
          local StopHOrg=StopH
          StopH = StopH + 24
          if (TestH <= StopHOrg) then -- if endhours has increased the currenthour should also increase
              TestH = TestH + 24
          end
      end

      local StartTVal = getMinutes(StartH, StartM)
      local StopTVal = getMinutes(StopH, StopM)
      local curTVal = getMinutes(TestH, TestM)
      return (curTVal >= StartTVal and curTVal <= StopTVal)
  end   

  local function IsNowBetween(StartH,StartM,StopH,StopM)
    local time = os.date("*t")
      if PrintLOG then
    log(time.hour, time.min) --check if time is correct, if not change to os.date("!*t") for UTC time
    end
    return IsTimeBetween(StartH, StartM, StopH, StopM, time.hour, time.min)
  end
    if PrintLOG then
      log(IsNowBetween(StartTimeHours, StartTimeMinutes, StopTimeHours, StopTimeMinutes))
  end
  -- https://stackoverflow.com/questions/35402992/how-to-check-if-current-time-is-between-two-others

objectupdatedate = (grp.find(objaddrCheckUpdateTime).updatetime) -- get update date for chosen object
diff = os.difftime(os.time(),objectupdatedate) -- difference between current time and objectupdatetime in seconds
if diff > SecondsPastTillRefetch then --check if last run was more than x seconds ago https://www.google.com/search?q=seconds+to+hours
      if PrintLOG then
          log('more than '..SecondsPastTillRefetch..' seconds since last run')
    end
    if (IsNowBetween(StartTimeHours, StartTimeMinutes, StopTimeHours, StopTimeMinutes)) then --check if current time is between hours that data can be fetched
        if PrintLOG then   
            log('data can be fetched')
      end
     
      -- https://forum.logicmachine.net/showthread.php?tid=4268&highlight=entsoe
      if fetchfortommorow then
        date = os.date('%Y%m%d', os.time() + 86400) -- date is tommorow, default setting
      else
        date = os.date('%Y%m%d') --date is today
      end

      periodstart = date .. '0000'
      periodend = date .. '2300'
      log(date)

      url = 'https://web-api.tp.entsoe.eu/api?securityToken='..SecurityTokenAPI..'&' ..
        'documentType=A44&in_Domain='..EntsoeDomain..'&out_Domain='..EntsoeDomain..
        '&periodStart=' .. periodstart .. '&periodEnd=' .. periodend

      data, code = require('socket.http').request(url)
      if not data or code ~= 200 then
        log('request failed', data, code)
        return
      end

      prices = {}
      key = 0

      callbacks = {
        StartElement = function(p, tag)
          if tag == 'TimeSeries' then
            key = key + 1
            prices[ key ] = {}
          elseif tag == 'position' then
            state = 'position'
          elseif tag == 'price.amount' then
            state = 'price'
          end
        end,
        EndElement = function(p, tag)
          state = nil
        end,
        CharacterData = function(p, value)
          value = value:trim()

          if #value > 0 then
            if state == 'position' then
              position = tonumber(value)
            elseif state == 'price' then
              prices[ key ][ position ] = tonumber(value)
            end
          end
        end
      }

      parser = require('lxp').new(callbacks)
      parser:parse(data)
      parser:close()

      if key > 0 then
        for i, price in pairs(prices[ 1 ]) do
          j = i - 1 -- shifts the first key from 1 to 0, so the first groupaddress is x/x/0 for 00:00 hour, next is x/x/1 for 01:00 hour,etc...
            grp.checkupdate(StartGrpAddr..'/' .. j, price) -- update objects with newest prices
          --log('uur ' ..j..' prijs: ' ..price)
        end
            
          -- https://stackoverflow.com/questions/69359710/how-to-get-the-smallest-number-in-a-table-with-lua
               lowestprice = math.min(unpack(prices[ 1 ])) --lowest number in table
                 highestprice = math.max(unpack(prices[ 1 ])) --highest number in table
     
            -- https://stackoverflow.com/questions/25835591/how-to-calculate-percentage-between-the-range-of-two-values-a-third-value-is
                --input = grp.getvalue('34/1/13')
          for i, price in pairs(prices[ 1 ]) do
         j = i - 1
             percentage = ((price - lowestprice) * 100) / (highestprice - lowestprice)
             --log('at hour '..j..':00 price is '..math.floor(percentage)..'% higher than lowest price today')
        grp.checkupdate(StartGrpAddrPercent..'/' .. j, percentage) --update objects with newest percentages
       
          if lowestprice == price then
              grp.checkupdate(StartGrpAddr .. '/26', StartGrpAddr..'/' .. j)
          end
          if highestprice == price then
              grp.checkupdate(StartGrpAddr .. '/27', StartGrpAddr..'/' .. j)
          end
        end
     
          -- https://www.codegrepper.com/code-examples/lua/lua+calculate+average+number
          function math.average(t)
            local sum = 0
                for _,v in pairs(t) do -- Get the sum of all numbers in t
              sum = sum + v
            end
            return sum / #t
          end
        grp.checkupdate(StartGrpAddr .. '/25', math.average((prices[ 1 ])))
        end
     -- https://forum.logicmachine.net/showthread.php?tid=4268&highlight=entsoe
     else
        if PrintLOG then
            log('data cannot be fetched yet')
      end
      end 
else
  if PrintLOG then
      log('data has already been fetched and updated, not necessary to re-fetch data')
  end
end