I used Grafana for some time now to display the charging statistics. While upgrading to influxdb2 I thought it would be nice to have the statistics in Home Assistant. So I looked at the different ways how to integrate the statistics and ended with the custom Apex Card. First I tried to fetch the data with the influxdb integration, but it seems it shows only a single value.
So I decided to give the data_generator a try and it worked perfectly – this is how it looks now:
And here is the code for the card:
type: custom:stack-in-card
cards:
- type: custom:mushroom-title-card
title: Ladeübersicht (30 Tage)
card_mod:
style: |
ha-card {
border: none;
padding-top: 8px !important;
}
- type: custom:apexcharts-card
stacked: true
graph_span: 30d
card_mod:
style: |
ha-card {
border: none;
padding-right: 20px;
}
apex_config:
tooltip:
enabled: true
x:
show: true
format: dd.MM.yyyy
series:
- entity: sensor.influx_ladezyklen_7d_strom
type: column
name: Strom
color: '#03a8f4'
show:
legend_value: false
unit: kwh
data_generator: |
var myInit = {
method: 'POST',
mode: 'cors',
headers: {
'Authorization': 'Token ...',
'Accept': 'application/csv',
'Content-type': 'application/vnd.flux',
'Accept-Encoding': '*',
},
body: 'from(bucket: "Wallbox") ' +
'|> range(start: -30d)' +
'|> filter(fn: (r) => r["_measurement"] == "Ladezyklen")' +
'|> filter(fn: (r) => r["_field"] == "Ladung (kWh)" or r["_field"] == "PV Strom (kWh)")' +
'|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") ' +
'|> map(fn: (r) => ({ r with "Strom (kWh)": r["Ladung (kWh)"] - r["PV Strom (kWh)"] }))' +
'|> keep(columns: ["_time", "Ladung (kWh)", "Strom (kWh)", "PV Strom (kWh)"])' +
'|> truncateTimeColumn(unit: 1d)' +
'|> group(columns: ["_time", "_measurement"])' +
'|> reduce(' +
' fn: (r, accumulator) => ({' +
' Strom: r["Strom (kWh)"] + accumulator.Strom,' +
' }),' +
' identity: {Strom: 0.0}' +
' )' +
'|> group()'
};
const request = async () => {
var result = [];
const res = await fetch("http://192.168.178.2:8086/api/v2/query?orgID=4f290f78b2f9939a", myInit);
if (res.status === 200) {
const data = await res.text();
var rows = data.split("\r\n");
for(var i=1; i<rows.length; i++){
var row = rows[i].split(',');
var _time = new Date(row[3]);
var _value = parseFloat(parseFloat(row[4]).toFixed(2));
if(row.length == 5) {
result.push([_time, _value]);
}
}
} else {
console.log(`Error code ${res.status}`);
}
return result;
};
return request();
- entity: sensor.influx_ladezyklen_7d_strom_pv
type: column
name: PV Strom
color: orange
show:
legend_value: false
unit: kwh
data_generator: |
var myInit = {
method: 'POST',
mode: 'cors',
headers: {
'Authorization': 'Token ...',
'Accept': 'application/csv',
'Content-type': 'application/vnd.flux',
'Accept-Encoding': '*',
},
body: 'from(bucket: "Wallbox") ' +
'|> range(start: -30d)' +
'|> filter(fn: (r) => r["_measurement"] == "Ladezyklen")' +
'|> filter(fn: (r) => r["_field"] == "Ladung (kWh)" or r["_field"] == "PV Strom (kWh)")' +
'|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") ' +
'|> keep(columns: ["_time", "PV Strom (kWh)"])' +
'|> truncateTimeColumn(unit: 1d)' +
'|> group(columns: ["_time", "_measurement"])' +
'|> reduce(' +
' fn: (r, accumulator) => ({' +
' PVStrom: r["PV Strom (kWh)"] + accumulator.PVStrom,' +
' }),' +
' identity: {PVStrom: 0.0}' +
' )' +
'|> group()'
};
const request = async () => {
var result = [];
const res = await fetch("http://192.168.178.2:8086/api/v2/query?orgID=4f290f78b2f9939a", myInit);
if (res.status === 200) {
const data = await res.text();
var rows = data.split("\r\n");
for(var i=1; i<rows.length; i++){
var row = rows[i].split(',');
var _time = new Date(row[3]);
var _value = parseFloat(parseFloat(row[4]).toFixed(2));
if(row.length == 5) {
result.push([_time, _value]);
}
}
} else {
console.log(`Error code ${res.status}`);
}
return result;
};
return request();
update_interval: 5m
- type: custom:apexcharts-card
update_interval: 5m
chart_type: donut
card_mod:
style: |
ha-card {
border: none;
padding-right: 20px;
padding-bottom: 20px;
}
graph_span: 30d
span:
end: day
apex_config:
legend:
show: false
dataLabels:
formatter: |
EVAL:function(value) {
return value.toFixed(0) + "%";
}
plotOptions:
pie:
donut:
labels:
show: true
total:
show: true
showAlways: true
formatter: |
EVAL:function(w) {
return w.globals.seriesTotals.reduce((a, b) => {return (a + b)},0).toFixed(1) + " kWh";
}
series:
- entity: sensor.influx_ladezyklen_7d_strom
name: Strom
color: '#03a8f4'
group_by:
func: sum
duration: 30d
show:
legend_value: false
unit: kwh
data_generator: |
var myInit = {
method: 'POST',
mode: 'cors',
headers: {
'Authorization': 'Token ...',
'Accept': 'application/csv',
'Content-type': 'application/vnd.flux',
'Accept-Encoding': '*',
},
body: 'from(bucket: "Wallbox") ' +
'|> range(start: -30d)' +
'|> filter(fn: (r) => r["_measurement"] == "Ladezyklen")' +
'|> filter(fn: (r) => r["_field"] == "Ladung (kWh)" or r["_field"] == "PV Strom (kWh)")' +
'|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") ' +
'|> map(fn: (r) => ({ r with "Strom (kWh)": r["Ladung (kWh)"] - r["PV Strom (kWh)"] }))' +
'|> keep(columns: ["_time", "Ladung (kWh)", "Strom (kWh)", "PV Strom (kWh)"])' +
'|> truncateTimeColumn(unit: 1d)' +
'|> group(columns: ["_time", "_measurement"])' +
'|> reduce(' +
' fn: (r, accumulator) => ({' +
' Strom: r["Strom (kWh)"] + accumulator.Strom,' +
' }),' +
' identity: {Strom: 0.0}' +
' )' +
'|> group()'
};
const request = async () => {
var result = [];
const res = await fetch("http://192.168.178.2:8086/api/v2/query?orgID=4f290f78b2f9939a", myInit);
if (res.status === 200) {
const data = await res.text();
var rows = data.split("\r\n");
for(var i=1; i<rows.length; i++){
var row = rows[i].split(',');
var _time = new Date(row[3]);
var _value = parseFloat(parseFloat(row[4]).toFixed(2));
if(row.length == 5) {
result.push([_time, _value]);
}
}
} else {
console.log(`Error code ${res.status}`);
}
return result;
};
return request();
- entity: sensor.influx_ladezyklen_7d_strom_pv
name: PVStrom
color: orange
group_by:
func: sum
duration: 30d
show:
legend_value: false
unit: kwh
data_generator: |
var myInit = {
method: 'POST',
mode: 'cors',
headers: {
'Authorization': 'Token ...',
'Accept': 'application/csv',
'Content-type': 'application/vnd.flux',
'Accept-Encoding': '*',
},
body: 'from(bucket: "Wallbox") ' +
'|> range(start: -30d)' +
'|> filter(fn: (r) => r["_measurement"] == "Ladezyklen")' +
'|> filter(fn: (r) => r["_field"] == "Ladung (kWh)" or r["_field"] == "PV Strom (kWh)")' +
'|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") ' +
'|> keep(columns: ["_time", "PV Strom (kWh)"])' +
'|> truncateTimeColumn(unit: 1d)' +
'|> group(columns: ["_time", "_measurement"])' +
'|> reduce(' +
' fn: (r, accumulator) => ({' +
' PVStrom: r["PV Strom (kWh)"] + accumulator.PVStrom,' +
' }),' +
' identity: {PVStrom: 0.0}' +
' )' +
'|> group()'
};
const request = async () => {
var result = [];
const res = await fetch("http://192.168.178.2:8086/api/v2/query?orgID=4f290f78b2f9939a", myInit);
if (res.status === 200) {
const data = await res.text();
var rows = data.split("\r\n");
for(var i=1; i<rows.length; i++){
var row = rows[i].split(',');
var _time = new Date(row[3]);
var _value = parseFloat(parseFloat(row[4]).toFixed(2));
if(row.length == 5) {
result.push([_time, _value]);
}
}
} else {
console.log(`Error code ${res.status}`);
}
return result;
};
return request();
For now the time range is fixed to 30 days. But I will implement a selectable time range next week.
