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.
1 Like
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")
2 Likes
@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
1 Like
@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.
1 Like
Hi Rachel, did you ever find a solution to this issue? I’ve just logged in and noticed the new tariff name is missing and I have no Agile rates since the beginning of January 
Trystan has promised to add the new tariff code, so hopefully it’ll appear soon.
1 Like
I’ve added this script to the usefulscripts repository, documented here usefulscripts/octopus at master · emoncms/usefulscripts · GitHub. This repository is available on standard emonSD (emonPi/2/HP) builds and can be updated to the latest version from the Admin > Update page or Admin > Components > usefulscripts to only update this feature. It still needs the manual crontab entry via command line to get it running though.
I’ve improved the script to also load the full rate history for the selected tariff. It will do this the first time that you run the script. E.g:
If you already have a feed in place with the script, the easiest way to use this is to delete the existing feed and then use this new version of the script from the usefulscripts repository. Here are the manual configuration steps via command line:
Update usefulscripts
cd /opt/emoncms/modules/usefulscripts
git pull
Create config file
cd /opt/emoncms/modules/usefulscripts/octopus/
cp default.agile.conf agile.conf
Enter configuration including emoncms server, write apikey, tariff_name and gsp_id (region):
nano agile.conf
Run script manually:
python3 load_agile_rates.py
Run script from crontab every evening at 5:03pm to pull in latest data:
03 17 * * * /usr/bin/python3 /opt/emoncms/modules/usefulscripts/octopus/load_agile_rates.py > /dev/null 2>&1
2 Likes
Support for this now added 
@TrystanLea Hi, have this all working thanks. As I have a home assistant entity that reports when the Octopus data is available, can I create a .sh (bat) file that I can run direct from my local emoncms instance (schedule) rather than having to link via ssh.
I assume this post may need to be transferred to a new topic, not sure how to do that?
Yes I think that should work via a bash script, just create a file called run_agile.sh and place in it the command
/usr/bin/python3 /opt/emoncms/modules/usefulscripts/octopus/load_agile_rates.py
What is the emoncms instance schedule that you mention?