Storing Electricity Price Data on AWS

For a personal project of mine, I like to be able to analyse the pattern of New Zealand's electricity Spot Prices; to identify the cheapest hours during the day to pull power from the grid, as well as, the best time of the day to sell back to the grid.

I will be creating a series of blogs as I build out the fragments of my project. Over time, I will integrate the individual fragments into a bigger overall solution. One of the drivers for analysing the New Zealand Spot Prices: is the aim in reducing the payback period of my Solar and Tesla Powerwall purchase. I have had the 2 systems for over a year at the time when this blog was published.

In this blog, I will explain how I will be collecting Spot Prices from electricityinfo.co.nz using one of their APIs; each Spot Price data reading will then be stored as a JSON file in an S3 bucket where it will be query-able using SQL. Using the same pattern, I will also track the actual cost per unit of power I am paying for from pulling power from the grid, my electricity provider is Flick Electric and I will also leverage their APIs to retrieve the pricing data.

An architecture diagram of the solution. The orchestration of retrieval and storage of the data using AWS serverless components.

Querying price data stored as JSON file in an S3 bucket using SQL in Athena.

The source code for this AWS SAM project can be found in my Github repository: https://github.com/chiwaichan/athena-spot-prices

In order for this solution to work you must have a set of credentials for Flick Electric, otherwise you can modify the SAM template to disable the Lambda Function's scheduler that triggers the Lambda to retrieve data. This Lambda function retrieves the credentials from AWS Secrets Manager, so you will need to create a Secret before deploying this solution as demonstrated in the AWS CLI shown in the screenshot below.

In a follow up blog, I will demonstrate the use of these Athena tables using a reporting service called QuickSight.