tedpearson/electric-usage-downloader

Import smart meter metrics from smarthub into VictoriaMetrics or InfluxDB

electric-usage-downloader

This project reverse engineers the NISC SmartHub api, which is used by hundreds of utility co-ops throughout the United States. This allows downloading 15-minute resolution electic usage and cost data for your personal account if you have a smart meter.

In prior versions of SmartHub, it was possible to download CSV exports of 15-minute interval usage data by specifying an "hourly" interval, but since January 2024 only hourly data has been available via CSV. That's why I reverse engineered the API instead of automating a download of the CSV as I had previously done.

Data can be imported into InfluxDB or VictoriaMetrics.

Config

Download config.example.yaml and fill in your own values.

• extract_days is how many days to look back from the current day. Max is 45. if specific --start and --end flags are not specified.
• account is your account number, available on your bill and on the SmartHub website.
• service_location is an internal SmartHub number, and must be retrieved from your browser:
  • Open the Developer tools to the Network tab
  • Navigate to Usage Explorer (example: https://novec.smarthub.coop/ui/#/usageExplorer)
  • Find a call to services/secured/utility-usage/poll in the Network tab
  • Open the call, and copy the serviceLocationNumber field from the Payload tab.
• timezone needs to be set to the timezone used by your utility. For some reason, the SmartHub API decided to return unix timestamps, but in the utility's timezone instead of in UTC, which would be the normal choice for an API.
• influxdb.insecure allows connecting to a server with certificate issues.
• The other fields should be fairly self-explanatory.

Running

• To download and insert the last extract_days, run like this arguments: electric-usage-downloader --config config.yaml
• To download and insert a specific date range, run with arguments: electric-usage-downloader --config config.yaml --start 2024-01-16 --end 2024-01-17
• The --debug flag can be used to log responses from the API for assistance debugging issues.

Details

The SmartHub api currently supports 15-minute resolution of data. This could change in the future; 15-minute interval usage data used to be available via CSV export, but that ability was removed in January 2024.

Measurement: electric

Fields:

• cost (in US cents)
• usage (in watts)

Dashboard

I have included my Grafana dashboard panel definition in the repo.

Features:

• Electric usage graphed in watts
• Trailing 1d and 7d averages
• Cumulative usage (right x axis)
• Integrated with data from my Ecobee thermostat, showing when my heat pump or aux oil heat is running.
  • See https://github.com/tedpearson/ecobeemetrics for how I get this data

Here's a screenshot of the dashboard panel in action:

electric-usage-downloader's Issues

Add timezone in config?

After importing the data, I noticed that the times in grafana were off by 2 hours. I'm in MST, and the offset being set in parser.go utilizes EST:

zone, err := time.LoadLocation("America/New_York")

I changed the timezone on this line, and that fixed the offset for me in grafana. It would be nice to have the tz option in config.yaml as well.

Data gets duplicated 60x in influxdb?

After importing the data, I noticed that the raw data included the same values for each minute of the hour. See screenshot:

The data returned from SmartHub (for me) seems to be hourly, see sample debug output:

DEBUG: Parsed data from poll endpoint:
&{Status:COMPLETE Data:map[ELECTRIC:[{Type:USAGE Series:[{Name:336288847 Data:[{UnixMillis:1719532800000 Value:2.26} {UnixMillis:1719536400000 Value:2.24} {UnixMillis:1719540000000 Value:0.97} 

I modified the local code to change the WriteMetrics function to write one point per hour which fixed the problem for me, and now I have single row for each hour with hourly value.

func WriteMetrics(records []ElectricUsage, config InfluxConfig) error {
	opts := influxdb2.DefaultOptions()
	if config.Insecure {
		opts.SetTLSConfig(&tls.Config{InsecureSkipVerify: true})
	}
	client := influxdb2.NewClientWithOptions(config.Host, config.AuthToken, opts)
	writeApi := client.WriteAPIBlocking(config.Org, config.Database)
	for _, record := range records {
		// Calculate the duration in hours, assuming the record spans an hour
		hours := record.EndTime.Sub(record.StartTime).Hours()
		if hours == 0 {
			hours = 1 // Avoid division by zero, assume at least one hour
		}

		// Create a point for the record
		point := influxdb2.NewPointWithMeasurement("electric").
			SetTime(record.StartTime).
			AddField("watts", float64(record.WattHours)/hours)

		if record.CostInCents != nil {
			point.AddField("cost", float64(*record.CostInCents)/hours)
		}
		if record.MeterName != nil {
			point.AddTag("name", *record.MeterName)
		}

		// Write the point to the database
		err := writeApi.WritePoint(context.Background(), point)
		if err != nil {
			return err
		}
	}
	return nil
}

I'm not sure if my utility provider returns different data vs. yours, but some customization here would be nice to control the point interval.

Support for influxdb v2?

Any chance you can add support for influxdb v2? It requires specifying the Org and Bucket ID/name.

Authenticating with SmartHub API...
Fetching data from SmartHub API...
Data not ready, retrying...
Data received, transforming...
Writing data to database...
panic: invalid: Please provide either orgID or org

goroutine 1 [running]:
main.main()
        /repos/electric-usage-downloader/main.go:9 +0x33
exit status 2

debug help needed.

Trying to get this to work with a different provider.... https://tcectexas.smarthub.coop - pretty sure it's the same backend code... pretty sure. My config.yaml has:

extract_days: 10
utility:
  api_url: https://tcectexas.smarthub.coop
  username: my-email
  password: my UNENCODED PASS ( from the oauth/v2 page... it's an unencoded password... )
  account: my-acct
  service_location: my-location
influxdb:
  host: https://localhost:8428
  auth_token: johndoe:influx_password
  database: db_name
  insecure: false

when I run:
go run main.go --config config.yaml
Start date: 2024-02-25 00:00:00 -0600 CST
End date: 2024-03-05 23:59:00 -0600 CST
Authenticating with Novec API...
panic: auth response did not include auth token

goroutine 1 [running]:
main.main()
/home/jack/electric-usage-downloader/main.go:9 +0x2b
exit status 2

any way to see if the code is sending the correct userid/passwd from the .yml file and what the actual packet send and received is? I no nothing about go.... a nice -Debug flag that showed the packets send and received would be really, really nice.

The oath/v2 call in the browser when it works sends:
userId=my-email&password=my-unencoded-pass
and gets:

{
    "status": "SUCCESS",
    "authorizationToken": "really-long-auth-token",
    "username": "my-email-address",
    "expiration": 1709660527,
    "primaryUsername": "my-email-address",
    "isSecondaryRegistration": false,
    "expiresIn": 299
}

if that helps / matters...

Translating API call to python results in error message

Really not an issue, more like a question. I've been trying to convert your code to python and then set it up to send MQTT messages so that home assistant can utilize the data. However, i keep getting "Your request could not be completed. If the problem persists, please contact customer service." Im thinking it has something to do with the poll_request body data, but not 100%. I'm able to get a authorization token without any issues. printing out the headers and the poll_request data looks the same as yours, but i keep getting that error. I was curious if you saw that while developing your tool and what you did to resolve it. I can copy and paste code into here if it'll help.

Doesn't apppear to support bidirectional / net meters

Hello there,

This project is amazing. Thank you so much for publishing this. I've been working (on and off) trying to reverse engineer the JSON response that SmartHub sends back to my browser so that I could automate something to grab the data daily and stuff it into a database for visualization.

The JSON response is so large I've always just given up after looking at it for a few minutes. This project helped me narrow in on exactly which part of the JSON response carries the actual consumption data... however as I look at the code and compare it to the JSON data I'm wondering if this may return incorrect data for some use cases. In my case, for example, I have a Net meter with Solar panels so when I go into SmartHub there are actually 3 consumption numbers: Consumption, Generation, and "Net". So for example let's say we're looking at say 9am and the sun is just barely up. During most of that hour I'm probably exporting a small amount of power, let's say 0.5 kWh, but then at 9:45 my wife turns on her hair dryer and we end up pulling 0.1kWh from the grid before 10am. The numbers for that would be:

Generation: 0.5 kWh
Consumption: 0.1 kWh
Net -0.4 kWh

I don't have an InfluxDB set up to test this code as is, but I've commented out the InfluxDB portion and have been just printing out the values and it it appears to me that it's hard coded to "Series[0]" in the code and when I look at the JSON document I get back in Postman when I call the API directly, I get TWO series arrays under usage:

                "series": [
                    {
                        "type": "column",
                        "color": "#00BB33",
                        "name": "658973 - Consumption",
                        "meterNumber": "658973",
                        "data": [
                            {
                                "x": 1713398400000,
                                "y": 0.43,
                                "enableDrilldown": true
                            },
                            <series data continues>
                    },
                    {
                        "type": "column",
                        "color": "#FF5319",
                        "name": "658973 - Generation",
                        "meterNumber": "658973",
                        "data": [
                            {
                                "x": 1713398400000,
                                "y": 0.0,
                                "enableDrilldown": true
                            },

If I'm understanding this code correctly; I think this will give me my "consumption" numbers only, but not my generation or my "Net" values.

Anyway: I just thought I would share my findings in case you're interested in having this code support solar / net meters. This has certainly given me what I need to get the data for myself so I deeply appreciate you publishing this!