Use downloaded half-hourly readings to do an Octopus Agile vs OVO price comparison

octopus_price_comparison.pl

#! /usr/bin/perl
#
# Use historic readings records by my SMETS smartmeter and downloard in MySQL with Octopus
# Agile per half-hour prices to do a 2023 price comparison.
#
use strict; use warnings; use v5.10;
use DBI;
use DateTime;
use Data::Dumper qw(Dumper);

my $octDailyCharge=0.4752;
#
# A few commonn functions
#
sub Sum { my $t=0; $t+=$_ for @_; return $t }
sub AnyUndef { foreach (@_) {return -1 unless defined $_;} return $#_}
sub vformat {my $fmt="\t".shift; return map {sprintf($fmt, $_)} @_; }
#
# Argument processing.  <No of debug cycles> <start date>
#
my ($cntDown, $startDate, $debug) = ($ARGV[0] // 0, $ARGV[1], 0);
#
# SQL queries to fetch the data from the CH system DB
#
my $getOctPrices   = "SELECT * FROM octopus";

my $getOVOdailies  = <<'ENDSQL';
    SELECT dts, Duse, cost, Rstand FROM daily_readings
    WHERE dts = ANY (SELECT dts FROM octopus)
ENDSQL

my $getOVOreadings = <<'ENDSQL';
    SELECT DATE(dts) AS "day" ,
        (UNIX_TIMESTAMP(dts) - UNIX_TIMESTAMP(DATE(dts))) DIV 1800 AS "slot",
        `use`
    FROM meter_readings
    WHERE date(dts) = ANY (SELECT dts FROM octopus)
ENDSQL
#
# connect to MySQL database load DB data into local Hash scructures
#
my $dbh    = DBI->connect("DBI:mysql(PrintError=>0,RaiseError=>1):$ENV{MYSQL_DB}",$ENV{USER},'');
#
# The Octopus table is a daily aggregate, with one row per day with the date then 48 prices
# for each half-hr slot These are imported into a Hash of day=>[48 prices]. Not that some
# prices can be NULL if missing in the Octopus REST query used to import the price data only
# reports a part day.
#
my %octPrices = map { my $d = shift @$_; $d => $_} @{$dbh->selectall_arrayref($getOctPrices)};
#
# The Ovo daily Hash is built from two tables. The first is recors the actual kWh used by
# half-hour slot in day. This is aggregated into a 48 vector in its {rates} entry. Again,
# there can be missing readings.
#
my %ovoDaily;
foreach my $row (@{$dbh->selectall_arrayref($getOVOreadings)}) {
    my ($d,$s,$use) = @$row;
    $ovoDaily{$d}{readings}[$s]=$use;
}
#
# The second collects some per day data reported by Ovo. The fields for daily use (Duse),
# cost (cost) and standing charge (Rstand) are copied into the ovoDaily date entry if
# there is readings data for that day.
#
foreach my $r (@{$dbh->selectall_arrayref($getOVOdailies, {Slice => {}})}) {
    if (exists  $ovoDaily{$r->{dts}}{readings}) {
        $ovoDaily{$r->{dts}}{Duse}   = $r->{Duse};
        $ovoDaily{$r->{dts}}{cost}   = $r->{cost};
        $ovoDaily{$r->{dts}}{Rstand} = $r->{Rstand};
    }
}
#
# There are a couple of days where some of the data elements needs to do pricing calcs are
# missing.  These odd days are discarded, so that subsequent calcs can assume complete data.
#
foreach my $d (sort keys %ovoDaily) {
    delete $ovoDaily{$d} if
        (not exists $octPrices{$d}) ||
        (not defined $ovoDaily{$d}{cost}) ||
        AnyUndef(@{$octPrices{$d}}) != AnyUndef(@{$ovoDaily{$d}{readings}});
}

print "Date\tUsed\tOVo\tOctopus\tOct Optimsed\n";
foreach my $d (sort keys %ovoDaily) {
    #
    # Set up startDate and debug filters on 1st pass
    #
    $startDate = $d unless defined $startDate;
    $debug = ($cntDown > 0) if $startDate eq $d;
    #
    # As a first cut we only sustain ~600W during offpeak. Anything else is time-shifted
    # load that has been scheduled during the cheap rate window, such as CH, DHW, dishwasher,
    # washing machine, and these in priciple could be time shifted to any 14 slots that are
    # the cheapest, so:
    #  -  Form a 14 elt array by removing the excess over 0.3 kWh into this and sort high to low;
    #  -  Add the i'th excess to the corresponding slot.
    #  -  Sort the price <=> slot hash into ascending price.
    #  -  Allocate the biggest excess to the cheapest Octopus priced slot ...
    #  -  Calculate the price data and print TSV summary
    #
    my @checks;
    my @timeshift;

    my $op    = $octPrices{$d};
    my $od    = $ovoDaily{$d};

    my $r     = $od->{readings};
    my $Duse  = $od->{Duse};
    my $cost  = $od->{cost} + $od->{Rstand};
    
    my ($octPrice, $totuse) = ($octDailyCharge, 0);
    $octPrice += 0.01*$op->[$_]*$r->[$_] for (0..47);
    $totuse   += $r->[$_]  for (0..47);

    my @summary = ($totuse,$cost,$octPrice);

    print "\nRrg    \t$d", vformat('%6.2f', @$r), "\n" if $debug;
    push @checks, Sum(@$r);

    foreach my $i (0..13) {
        $timeshift[$i] = $r->[$i]>0.3 ? $r->[$i] - 0.3 : 0;
        $r->[$i] -= $timeshift[$i];
    }

    my @srtTimeshift = sort { $b <=> $a } @timeshift;
    my @priceNdx     = (0..47);
    my @srtPriceNdx  = sort { $op->[$a] <=> $op->[$b] } @priceNdx;
    
    push @checks, Sum(@$r) + Sum(@srtTimeshift), Sum(@$r), Sum(@srtTimeshift);
    if  ($debug) {
        my @ndx = (' ') x 48;
        $ndx[$srtPriceNdx[$_]] = sprintf('%2d', $_) for (0..13);
        my @xtra= ('     ') x 48;
        $xtra[$srtPriceNdx[$_]] = sprintf('%6.2f', $srtTimeshift[$_]) for (0..13);
        
        print "Rrg    \t$d", vformat('%6.2f', @$r), "\n";
        print "Clp Rdg\t$d", vformat('%6.2f', @timeshift), "\n";
        print "Srt Clp\t$d", vformat('%6.2f', @srtTimeshift), "\n";
        print "Prc    \t$d", vformat('%6.2f', @$op), "\n";
        print "Srt Prc\t$d", vformat('%6.2f', map({ $op->[$_]} @srtPriceNdx)), "\n";
        print "Ndx    \t$d", map ({"\t $_   "} @ndx), "\n";
        print "Rng Inc\t$d", map ({"\t$_"} @xtra), "\n";
    }
    $r->[$srtPriceNdx[$_]] += $srtTimeshift[$_] for (0..13);
    print "Rrg NEW\t$d", vformat('%6.2f', @$r), "\n" if  $debug;
    
    push @checks, Sum(@$r);
    my $check = $checks[$#checks] - $checks[0];
    $check = - $check if $check < 0;
    die "Total readings mismatch after rate time shifting" if $check > 0.1;
    
    my $octNewPrice = $octDailyCharge;
    $octNewPrice += 0.01*$op->[$_]*$r->[$_] for (0..47);
    printf "%s\t%6.2f\t%6.2f\t%6.2f\t%6.2f\n", $d, @summary, $octNewPrice;

    exit if $debug && --$cntDown == 0;
}