Hi, is there anyway to import the octopus agile tariff rates for my region into a new emoncms feeds table
Regards John
1 Like
I have wondered about this too.
There is an “Agile App”:
But there are other use cases where it might be useful to have Agile data as a feed.
Yes, I had seen the app but am also very interest to get the data as a separate feed
I have this php script that I run locally to populate a single MYSQL feed:
You’ll need to set the db password, pick the right feed id, and select an appropriate product code. Oh, and an Octopus API Key.
agile.php
<?php
$db = new mysqli("localhost", "emoncms", "<password>", "emoncms");
date_default_timezone_set("Europe/London");
# id of MYSQL feed to write into
$feed_id = 123;
# documentation: https://developer.octopus.energy/rest/reference
$url = 'https://api.octopus.energy/v1/products/AGILE-23-12-06/electricity-tariffs/E-1R-AGILE-23-12-06-M/standard-unit-rates/';
$api_key = "<Octopus API Key>";
$results = fetchJSON($url, $api_key)->results;
#file_put_contents("/home/squidutils/octopus/debug.json", json_encode($json, JSON_PRETTY_PRINT));
$today = strtotime(strftime('%x')) + 24 * 3600;
for ($i = 0; $i < count($results); $i++) {
$r = $results[$i];
$from = strtotime($r->valid_from);
$until = strtotime($r->valid_to);
if ($until <= strtotime("2024/02/03 00:00:00")) { break; }
#printf("%s-%s: %.3f\n", strftime('%x %H:%M', $from), strftime('%H:%M', $until), $r->value_exc_vat);
$db->query(sprintf("REPLACE INTO feed_%d (time, data) VALUES (%d, %f)", $feed_id, $from, $r->value_inc_vat));
if ($i == 0) { # first is actually last, so add extra point beyond that to make step chart look better
$db->query(sprintf("REPLACE INTO feed_%d (time, data) VALUES (%d, %f)", $feed_id, $until, $r->value_inc_vat));
}
}
function fetchJSON($url, $api_key) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_ENCODING, 'gzip, deflate');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_USERPWD, "$api_key:");
$content = curl_exec($ch);
#print_r($content);
return json_decode($content);
}
?>
Graphs well with Step type:
PS: there is a script in usefulscripts called agile.py, but this is incorrectly named and is for downloading meter data from Octopus, not prices.
Tim (Timbones), thanks for this, unfortunately I haven’t ever run php commands in emoncms before. Can I pick your brains and ask a couple of questions
1- where do I get the db password
2- where do i file the agile.php file
3- by product code i assume you mean the agile tariff code
4- how do i activate the php command file
5- is there anything else I need to setup that will be needed
ps i have the octopus api key
1- you’ll find the db password in settings.ini
2- you can put the file anywhere, e.g. in the home folder
3- yes, the Agile product code + region
4- login via ssh, and do php agile.php
you’ll ultimately want this run daily via cron
5- you’ll need to create a new MYSQL feed in Emoncms. If that type isn’t available, check settings.ini
Some command line tips here to get you going.
@TrystanLea may have a neater solution
@Timbones @TrystanLea
regarding question 5 how do i create a new MYSQL feed in emoncms?
emoncms agile error.pdf (10.5 KB)
At the bottom of the Feeds page is a button marked + New Feed. Click it, and enter in a name and tag, and select MYSQL Timeseries:
Then, make note of the ID of the new feed - you’ll need to put that number in the script.
Caveat: this may not be the best way to import Agile prices, and it hasn’t been tested on an EmonPi, but it’s what I use on my non-Pi instance.
I’ve created a python script to hopefully help with this, not to take away from your solution
@Timbones and appreciate your help for @JohnSchols on this.
This script can be used to post to a local or remote emoncms instance. It creates the feed if it doesnt exist to save on that step as well. See configuration options at the top.
import requests
from datetime import datetime
import sys
import json
# Emoncms account details
host = "https://emoncms.org"
apikey = "WRITE_APIKEY"
# AGILE-18-02-21, AGILE-22-07-22, AGILE-22-08-31, AGILE-23-12-06, AGILE-VAR-22-10-19, AGILE-FLEX-22-11-25
tariff_name = "AGILE-23-12-06"
# Regions
"""
"A": "Eastern_England",
"B": "East_Midlands",
"C": "London",
"D": "Merseyside_and_Northern_Wales",
"E": "West_Midlands",
"F": "North_Eastern_England",
"G": "North_Western_England",
"H": "Southern_England",
"J": "South_Eastern_England",
"K": "Southern_Wales",
"L": "South_Western_England",
"M": "Yorkshire",
"N": "Southern_Scotland",
"P": "Northern_Scotland"
"""
gsp_id = "D"
feed_name = f"{tariff_name}-{gsp_id}"
# Get feedid of agile rates feed from emoncms
url = f"{host}/feed/list.json?apikey={apikey}"
response = requests.get(url)
if response.status_code == 200:
feeds = response.json()
feedid = None
for feed in feeds:
if feed['name'] == feed_name:
feedid = feed['id']
break
# if feed not found, create a new feed
# https://emoncms.org/feed/create.json?tag=Octopus&name=AGILE-23-12-06-D&engine=5&options={"interval":1800}
if feedid is None:
print ("Creating agile feed")
options = { "interval": 1800 }
# convert options to json
options = json.dumps(options)
url = f"{host}/feed/create.json?tag=Octopus&name={feed_name}&engine=5&options={options}&apikey={apikey}"
print (url)
response = requests.get(url)
if response.status_code == 200:
feedid = response.json()['feedid']
url = f"https://api.octopus.energy/v1/products/{tariff_name}/electricity-tariffs/E-1R-{tariff_name}-{gsp_id}/standard-unit-rates/"
response = requests.get(url)
if response.status_code == 200:
result = response.json()
if result is not None and 'results' in result:
data = []
for row in result['results']:
date = datetime.fromisoformat(row['valid_from'].replace('Z', '+00:00'))
data.append([int(date.timestamp()), row['value_exc_vat']])
# sort by timestamp asc
data.sort(key=lambda x: x[0])
# Send data to emoncms feed/post API
url = f"{host}/feed/post.json?id={feedid}&apikey={apikey}&data={data}"
response = requests.get(url)
if response.status_code == 200:
print("Data uploaded successfully")
1 Like
@TrystanLea Trystan, Thanks for this. Not having made changes within emoncms, running locally, before, which part of the above do I post (named agile.php i assume) and where do I post it?
Hi Trystan,
My Agile App stopped working on 1st Jan becuase the tariff code has changed. The new one is not in the list in the app. It’s:
AGILE-24-10-01
Is it possible to add thi in?
Rachel
@timbones can you please point me in the right direction for creating/running a cron job
Type crontab -e - this will open the current cron settings in a text editor.
Add a line like the following to run my script at 3 minutes past 5pm every day…
03 17 * * * php /home/pi/agile.php
…or like this to run Trystan’s script:
03 17 * * * python3 /home/pi/agile.py
Then save the changes and exit.
Adjust the path of the script to wherever it has been saved to.