CONNECT - RESPOND - SAVE MONEY - COMBAT CLIMATE CHANGE
I was inspired by the transition that is underway in energy systems around the world, driven by falling renewable energy costs and innovations in consumer energy pricing. My application - which I've called My Energy Planner - aims to connect people with this transition. I want to provide a tool that will help people see and respond to changes in the system, so that they can save money and help to combat climate change.
What it does
At the heart of the application are two sets of data that are retrieved via APIs.
The first set of data captures the carbon intensity of the UK's electricity system. This is a measure of the carbon dioxide produced when generating each unit of electricity. It changes constantly depending on demand in the system and the energy mix. As more of the electricity we use is generated from renewable sources, so the carbon intensity of the grid reduces - between 2013 and 2019 it reduced by nearly 60%. If we look at daily data we can see how variation in output from wind, solar and other low-carbon energy sources impacts on grid carbon intensity.
The second set of data captures what are called time-of-use electricity prices. These are linked to the wholesale price of electricity and also vary depending on demand and the energy mix. Homeowners with smart meters can switch to time-of-use pricing and potentially save money by moving their consumption away from periods of peak demand. By doing so they help to reduce reliance on fossil fuels to meet the peaks in demand and thereby contribute to efforts to combat climate change.
My application makes it easy for people to model their electricity consumption and test whether they would benefit by switching to time-of-use pricing. This is done by creating a daily planner. These can be set up to replicate automatically, allowing the user to see the changes in costs and carbon footprint from day to day.
As both prices and carbon intensity data are available in advance, people can also plan ahead and modify their consumption to maximise their savings and reduce their carbon footprint.
How I built it
- Core tables
At the heart of the application are tables to hold carbon intensity values and tariff prices for half-hourly time periods. By changing the key of the time periods to the start time of the period, new records in the carbon intensity values and tariff prices tables are automatically linked to the corresponding period.
The half-hourly time periods are generated for each day by automations that copy a set of records in the half-hour periods table.
Users can create daily planners to model their electricity use. Each timed activity they add to a planner captures a period of electricity use that has an associated starting time period and therefore corresponding tariff price and carbon intensity value. A table of activity types holds a library of activities that users can select from or add to.
Carbon intensity values and tariff prices are pulled into the application on a daily basis using pipelines. Two pipelines pull in actual grid carbon intensity data for the past 24 hours and forecast values for the next 48 hours. Another pulls in time-of-use prices for the next day.
As explained below, I then created additional pipelines to record the raw JSON returned by the APIs.
I use automations to create a record for each day and then to populate the time periods for the day.
Users can flag that daily planners should be automatically replicated. One automation handles the replication of the daily planners and another the replication of the associated timed activities.
There are more automations to respond to changes made to timed activities using the Kanban report. As explained below, I was keen to make the Kanban as intuitive as possible and used automations to link timed activities to specific time periods after they have been moved on the Kanban report. More automations handle the movement of timed activities from one row of Kanban cards to the next.
- Code Pages
I used several code pages to provide interactive charts, gauges and related information on the dashboard.
One page create the custom charts and supporting information that allow users to step through days to see how prices and carbon intensity vary over time. Another creates gauges showing the time-of-use price and grid carbon intensity value for the current half-hour period.
There are two more code pages on the dashboard, showing both details of daily planners and comparisons day-by-day. Finally, an additional standard QB chart shows the breakdown of consumption by category.
- Formula Fields
In addition to the formula fields that process raw carbon intensity data values and raw price values, I created a number of function fields on the timed activity table to enable the calculation of costs and carbon footprint in each half-hour period in the corresponding day. These are described in the next section.
Challenges I ran into
I realised that I had used up a significant chunk of my pipeline quota in development and would run out of quota in a matter of weeks. I created additional pipelines to write raw JSON to raw carbon intensity data values and raw price values and formula fields to parse the JSON and create records in tariff price and carbon intensity value tables. As a workaround, it seems to work okay.
I wanted to create charts that would allow users to step through tariff prices and carbon intensity values on a day-by-day basis. I also wanted to create charts from data contained in columns in the same record, rather than separate records. I found that I could meet all the charting requirements using a charting library (chart.js).
The next challenge was to be able to calculate the cost and carbon footprint of each timed activity, because when an activity lasts more than 30 minutes the calculation requires information from several records. I was keen to create a solution that would calculate values as soon as a record was created and that could also respond to changes in the start time.
To do this I concatenated the period in the day (from 1 to 48) and the price in each record and then used a Combined Text summary field at day level to create a single text field containing every period and price - e.g. 01~5.33 02~3.45 03~4.29 etc. I passed this information back to each time period and its related timed activities. Now I could use formula fields to determine which of the periods were relevant for the cost calculation and pull out the corresponding prices for the calculation - e.g. if the timed activity was active for the 3rd period in the day I could pull out the price for the 3rd period and multiply it by the consumption to determine the cost in that period.
- I also wanted to provide the ability to easily move a timed activity to a different start time. I like QB Kanban reports and wanted to provide this functionality through a Kanban report. The challenges here were to allow users to access 48 separate columns in a Kanban (one for each half-hour period) and to recalculate values without requiring a refresh of the screen.
To create a Kanban I had to create a new text-select field containing every half-hour period in the day. An automation fires when a timed activity is moved to a new half-hour period to link it to the correct time period. The calculations of costs associated with the timed activity are either based on the newly selected start time (if it has just been moved on the Kanban) or the link to the time period. Although this took some time to think out and implement, it works really well and it does not rely on an automation completing to provide feedback to the user.
I also had to resolve the challenge of dealing with 48 half-hour periods and a maximum of 15 columns. My solution breaks the day down into four 6-hour segments, each with its own embedded Kanban board. Activities can be moved between Kanban 'rows' by dropping them into a card for the previous or next six-hour period and refreshing the screen. This seems to work well.
Accomplishments that I'm proud of
It wasn't immediately obvious how to access time-of-use price and grid carbon intensity values from multiple time periods for the calculations of cost and carbon footprint for each timed activity. I was keen to avoid creating another table to hold half-hour periods for each timed activity (which would have significantly complicated the design) and to avoid using code linked to buttons, so was pleased with my solution.
I was also really pleased to create a solution for moving a timed activity to a new start time that was based on Kanban reports and which avoided screen refreshes as much as possible.
Finally, I think the application is visually quite engaging and interactive thanks to code page 'widgets' that I built using chart.js and some Bootstrap-inspired layout.
What I learned
I learned that with some perseverance at the design stage I could make use of the intuitive nature of Kanban boards to simplify the user's interaction with the application. At a few points I doubted whether this could be done, but I'm pleased with the end result.
Overall, I think it's given me more confidence to integrate code pages, pipelines and automations with the features that make Quick Base such a great platform.
What's next for My Energy Planner
The application could be extended further, pulling in actual consumption data collected by smart meters. As we have recently had a smart meter installed, that's next on my list. By pulling in this data, users would have an alternative to manually building a daily planner if they wanted to look at potential and actual savings from a time-of-use tariff.