Data analysis in Excel

When I was on the standard variable Octopus tariff I regularly downloaded my data from the GE Cloud and kept it in a spreadsheet and calculated how much I was saving vs not having PV and a battery. I moved to Cosy and am now on Flux and my Excel skills have reached their limit due to the rate change depending on time of day.
From a previous job, in Excel I know that it's possible to copy data into a reusable form that populates a database and from there you can recall data for given dates/times (pivot tables I think?). However I didn't write these sheets, only used them, I could probably reverse-engineer one (I don't have any of these spreadsheets from that job).
Does anyone have such a spreadsheet whose template they would share or point me in the direction of where to acquire the knowledge to make my own (Youtube? - I've searched but don't seem to be using the correct nomenclature to find what I'm after)?
One issue with the GE data is the time format, I'm pretty sure that Excel will get confused by that, so it would need to be converted by adding AM/PM appropriately or into 24hr format, again a more automated method in Excel would be preferable.

Have you tried using the smart tariff card and the reports that are available on the portal.
First set up you Octopus smart tariff card. On the main dashboard, scroll down to just below the weather tile. Open up and select Octopus energy from the providers listed. You will need to enter you MPAN number, Octopus account number and Octopus API (I'm not sure if you need to have activated your GIV Energy API as well - perhaps someone else can advise). This then lets you see how much you are importing and exporting from your flux tariff without having to do any calculations. If you open the tile up (weird arrowy thing in the top right hand corner) you can see the total in Kw and £ and readings for each half hourly slot - switch between import and export by clicking the arrow at the edge of the graph.
If you go to the reports option on the main portal (scroll right down the bottom) - you can select custom report. Choose the date range you want to check and click enter. The top right hand tile will give you your consumption for the period and the total imported and exported in monetary terms

To find you Octopus API key - log onto your Octopus account. Scroll right down to the bottom and there is an option to go to the "Old Dashboard". Click on this. Then open up you account details. This shows you your account settings. There are lots of cards - to change your password, contact details etc. Open the one marked developer settings - this will show you your API key

Karen Thank you Karen, I haven't used the smart tariff card, I will check it out.

Update I've set that up and it is useful to see, I'd still like to figure out how to have it all in a spreadsheet so that I can compare the benefits of various tariffs and also work out savings and pay back periods etc...

Under automations, my only option is 'Stop Automation', prior to setting the card up I am on Eco with the battery set to charge to 100% between 3am and 5am. I assume that if I select 'Stop Automation' it will clear this and then present other options?

I couldn't find anything specific either using my google-fu.

I personally just bring in the data into Google Sheets and have a semi auto process that I mess with it myself. Haven't needed to use anything complex for a long time.

Here are some very good resources for Octopus Energy Prices
https://energy-stats.uk/
https://mysmartenergy.uk/
https://www.guylipman.com/octopus/formulas

Those 3 at least will allow you to get all of Octopus Tarriffs

Getting tariffs for other providers is really difficult.

I checked whether Go v Flux would be better only today for the period 13 Feb to 13 March

My Curent Octopus Go
Unit rate (04:30 - 00:30): 39.35p/ kWh inc VAT
Unit rate (00:30 - 04:30): 7.5p/ kWh inc VAT

Trying to reduce the numbers to the most basic values.

Night Rate (0:30 to 04:30) Load Shifting : 251.8kwh
Day Rate: 43.9kwh
Total: 295.7kwh @ £36.16
Gives an Average Unit Rate = 12.23pkwh
Grid Export SEG (British Gas): 59.39kwh @ 6pkwh = -£3.56
Net Average Unit Rate = 11.02pkwh

My Flux Rates (East Midlands) would be:
Import (inc VAT)
Day: 32.4
Flux 02-05:00: 19.44
Peak Rate: 45.36

Export (inc VAT??)
Day: 21.4
Flux 02-05:00: 8.44
Peak Rate: 34.36

So the numbers work out to:

Day: Consumption 159.36 @ 32.4 = £51.63
Flux: Consumption 130.01 @ 19.44 = £25.27
Peak: Consumption 6.26 @ 45.36 = £2.84
Total Bill Consumption would be £79.74

I use British Gas for my SEG so don't have 30min amounts.

Lets look at each of the 3 possible Export scenarios:

Current SEG Export: 59.39kwh
@ Day Rate 21.4p = -£12.71
@ Flux Rate 8.44p = -£5.01
@ Peak Rate 34.36p = -£20.41

So my Net Bill would be

@ Best £59.33 (average rate of 20.07pkwh)
@ Median £67.03 (average rate of 22.67pkwh)
@ Worst £74.73 (average rate of 25.28pkwh)

Current East Mids Go Rates are:
Unit rate (04:30 - 00:30): 42.46p/ kWh
Unit rate (00:30 - 04:30): 12.00p/ kWh

Night Rate (0:30 to 04:30) Load Shifting : 251.8kwh
Day Rate: 43.9kwh
Total: 295.7kwh @ £48.86
Gives an Average Unit Rate = 16.52pkwh
Grid Export SEG (British Gas): 59.39kwh @ 6pkwh = -£3.56
New Average Unit Rate = 15.32pkwh

So still would have been worse off using Flux v Go (current rates) by £14.03 to £29.43 during Feb to March 2023.

A couple of youtubers have mentioned tariff comparison spreadsheets you can download. (I've not tried them myself.)
https://www.youtube.com/watch?v=iLFrykG49Uk
https://www.youtube.com/watch?v=y8d1iqnhuek