Bids and offers published on EMI - file structure to be modified

  • 3K Views
  • Last post 13 June 2022
Phil Bishop posted this 01 December 2021

Please be advised that structure of the bids and offers files that we publish on EMI datasets is about to change.

The current structure will continue to be published in the folders called Bids and Offers, respectively, for a couple more weeks. The new structure is already available in the folders called BidsNew and OffersNew, respectively.

On 14 December 2021, the following will occur:

  • the folder currently called Bids will be renamed BidsOld   
  • the folder currently called Offers will be renamed OffersOld
  • the folder currently called BidsNew will be renamed Bids   
  • the folder currently called OffersNew will be renamed Offers

Then on 21 December 2021, the folders called BidsOld (i.e. currently called Bids) and OffersOld (i.e. currently called Offers) will stop being updated and will be be deleted.

In other words, users of the Bids and Offers data need to tweak their systems to accomodate the schema changes within the next two weeks.

This modification has been made as part of our data migration from a SQL Server instance to a Databricks-based Delta Lake, and to bring the schema nomenclature into line with that used

throughout our Delta Lake and other EMI datasets.

The old and new schemas can be seen in the attached file.

Attached files

  • Liked by
  • Tuong Nguyen
  • sarahafeez
Order by: Standard | Newest | Votes
vspd_user posted this 11 June 2022

Phil,

These files are great. It's excellent to see the offer revisions through time. 

We've noticed an issue though in that the files don't appear to be aligning with WITS. I have one example and have not looked to see how widespread the promlem is.

In the file for 20220601, for participantCode = 'GENE', Unit = 'HLY2', TradingPeriod = 1
It shows one offer submission time (for 5 tranches) of '2022-05-25', '13:03:42.000'  and the isLatest flag = Y

However for the same date, trading period, station and unit, WITS final_offer file shows different offers, with created date of "2022-05-31 02:17:12+12:00"

It's possible there's an issue with your data pipeline. I understand there may have been a WITS outage during the time between 2022-05-25 and gateclosure on 2022-05-31, but there are other offers in the emi data that appear to have been submitted during that period.

It would be great if the the data files could be back-filled with all of the offers and/or the discrepancy with WITS resolved.

Attached files

Phil Bishop posted this 12 June 2022

Hi vspd_user

I don't think we've got it wrong. But if you're not persuaded please do let us know.

In a nutshell, we only present meaningful records and suppress all records that don't tell us something that we didn't already know from a previous submission. I'll expand on what I mean by this further below. In doing so, I'll refer to the attached Excel file containing three tabs as follows:

  • From WITS as per vspd_user (let’s call it ‘vspd_user WITS’)
  • From WITS as per EA  (let’s call it ‘EA WITS’)
  • From EMI (i.e. taken from what the EA publishes on EMI).

The attached spreadsheet only contains data pertaining to HLY2201 HLY2 offer submissions by GENE for trading period 1 on 6 June 2022, i.e. the focus of your question above.

The first point to note is that the structure of the file you have obtained from WITS (or constructed yourself from data that you've downloaded from WITS) is different to the file we receive each day from WITS. In the vspd_user WITS file there is only a single record that relates to HLY2201 HLY2 offer submissions by GENE for trading period 1 on 6 June 2022 whereas in the EA WITS file there are 51 records. Some of this difference is explained by the fact that you appear to show only energy offers whereas the EA file contains energy, FIR and SIR offers. Also, the EA WITS file has each band or tranche of the offer on a new line whereas the vspd_user WITS file shows the bands as columns.

In any event, the vspd_user WITS file shows only one energy offer – the offer submitted at 2:17am on 31 May. The EA WITS file shows three energy offer submissions: 1:03am on 26 May, 5 past midnight on 31 May, and 2:17am on 31 May (see rows 20-34 of the EA WITS tab of the attached file). Significantly, the MW associated with all three energy offer submissions is zero in all tranches. Not so significant is the fact that while the prices for all tranches and submissions are non-zero, the price of some tranches differs across the three submissions. The energy offer prices in this situation are really of no consequence because nothing is being offered. The max ramp up and down values are zero for all three offers as well (this is curious but of no practical significance because, as just noted, no megawatts are being offered and it’s not germane to the question at hand).

The key point to note from all of this is that the second (5 past midnight on 31 May) and third (2:17am on 31 May) energy offer submissions don't change anything from what was initially offered, i.e. zero megawatts are being offered in all tranches in all three submissions. In other words, the second and third energy offer submissions don't tell us anything we didn't already know. Yes, some prices in some tranches are changing with each submission but it’s the price of nothing! To be sure, the second and third offers are significant and meaningful when it comes to the FIR and SIR components of these offers but given the data you attached to your post I understood your question was related to only energy offers.

If you look at the third tab in the attached spreadsheet, you’ll see that in the file we publish on EMI we show only the first energy offer submitted, i.e. the one submitted at 1:03pm on 25 May UTC time (or 1:03am on 26 May NZ time). Moreover, we flag it as the latest submitted offer. Again, that’s because the energy submissions that follow are meaningless. If we published every meaningless change to submitted offers, the file that we publish would be enormous. 

I hope this makes sense.

Cheers

Phil

P.S. - three CSVs uploaded with names as per bullet points above. Can't upload an .xlsx file.

Attached files

vspd_user posted this 13 June 2022

Thanks for the detailed response Phil,

We weren't expecting any logic to have been applied so hadn't checked for the very resonable case that you've highlighted (where prices have changed in 0MW offers).

Can confirm that if we ignore the times where wits_mw = 0 and emi_mw = 0 then we don't see any discrepancies.

The rest of the differences you point out are just file structure differences (the WITS files we referred to are seperate for energy and reserve and in wide format, only showing the last offer).

Thanks again.

Phil Bishop posted this 13 June 2022

Just to be very explicit, we do consider the first submitted offer for any trading period to be significant, or meaningful, and therefore include it in our records and in the files we publish on EMI. Even when the MW are zero in all tranches. Arguably this is overkill.

Thereafter our logic essentailly states: any submitted offer in any tranche for any trading period at any location by any participant is the same as the first submitted offer in any tranche for any trading period at any location by any participant - until it isn't (i.e. until a subsequent submission changes it).

Phil