Excel file format cannot be determined

[mdavis-xyz]

I'm running some code which used to work. Now I get an error. I think the URL for the participant registration list is wrong.

Steps to reproduce

• create a new Python virtual environment (3.12.3)
• pip install nemosis
• create a new empty folder for the data cache
• Run this script:

from nemosis import static_table

nemosis_data_cache = "/home/matthew/Data/nemosis/"

static_pd: "pd.DataFrame" = static_table(
    "Generators and Scheduled Loads", nemosis_data_cache
)

print(static_pd)

Expected behavior: Script runs without error, and prints the static data.

Actual behavior:

INFO: Retrieving static table Generators and Scheduled Loads
Traceback (most recent call last):
  File "/home/matthew/Downloads/nemosis-dep/main.py", line 5, in <module>
    static_pd: "pd.DataFrame" = static_table(
                                ^^^^^^^^^^^^^
  File "/home/matthew/Downloads/nemosis-dep/venv/lib/python3.12/site-packages/nemosis/data_fetch_methods.py", line 297, in static_table
    table = static_file_reader_map[table_name](path_and_name, table_name)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/matthew/Downloads/nemosis-dep/venv/lib/python3.12/site-packages/nemosis/data_fetch_methods.py", line 404, in _read_excel
    xls = _pd.ExcelFile(path_and_name)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/matthew/Downloads/nemosis-dep/venv/lib/python3.12/site-packages/pandas/io/excel/_base.py", line 1554, in __init__
    raise ValueError(
ValueError: Excel file format cannot be determined, you must specify an engine manually.

Debugging Info

The installed libraries are:

• pandas: 2.3.3
• nemosis: 3.8.1
• openpyxl: 3.1.5
• pyxlsb: 1.0.10

The docs for pd.ExcelFile say that something changed in version 1.2.0, but that was superseded years ago.

The Cause

The AEMO participant registration file is a .xlsx. It has been that way for over a year

However the code here has a url ending in .xls.

https://www.aemo.com.au/-/media/Files/Electricity/NEM/Participant_Information/NEM-Registration-and-Exemption-List.xls

I do not know where this came from. When I open that URL with firefox, I see the file prompt suggests a .xlsx ending.

So I think we were passing a filename to Pandas which ended with .xls, but the content was actually .xlsx. Maybe Pandas was previously smart enough to figure it out, but now expects us to be more explicit. Although when I try to use older versions of Pandas and xlrd, I get the same error.

Proposed Solution

I do not think we need to actually specify an engine. Rather we should just fix the URL.

Either:

• append an x to the URL; or
• replace the whole URL with https://www.aemo.com.au/-/media/files/electricity/nem/participant_information/nem-registration-and-exemption-list.xlsx

And change the filenames we save this to (e.g. here)

[mdavis-xyz]

xls vs xlsx

I've fixed the xls vs xlsx thing in #61, although I am not able to successfully test it.

HTTP status check

I just tried with those proposed changes. Whilst I do believe the file formats are being mixed up (and thus should be fixed), the real cause of the error is that AEMO is returning a 403 error. Nemweb does not check HTTP statuses, so saves the error HTML as an Excel file, which cannot be read as an excel file.

The next step in the solution is to check the HTTP status, instead of assuming it's always successful. Since we're using requests, adding r.raise_for_status() after each .get() should work. I will add this to the open PR #48, otherwise the merge would be a little messy.

There's a function

def status_code_return(url):
    r = requests.get(url, headers=USR_AGENT_HEADER)
    return r.status_code

This is never called. (Also, downloading an entire file to check the status is inefficient. We can do a HEAD for that purpose if we ever need to.)

Why 403?

Now the next question is why is AEMO giving us 403 errors? My guess is that they recently cranked up the security of their website, and forgot that we need automated access for this file. (Why on earth do they not just publish this data through the usual MMS channels? I don't know.)

At first I thought it's because I'm outside Australia. No, with a VPN to Australia I am still blocked.

Looking at the Wayback machine:

June 13 2025: URL is https://www.aemo.com.au/-/media/files/electricity/nem/participant_information/nem-registration-and-exemption-list.xlsx?la=en which the Wayback machine sucessfully downloaded

Aug 14 2025: URL is https://www.aemo.com.au/-/media/files/electricity/nem/participant_information/nem-registration-and-exemption-list.xlsx?rev=1a0da5b272cf452382f01f01b00b445f&sc_lang=en which the wayback machine failed to download

Given that the Wayback machine ceased to sucessfully download the file, I think AEMO changed something in terms of security around then. (Probably to block the new onslaught of LLM traffic.)

After playing around, I can see that they're blocking based on user agent. A solution is to put in a standard Chrome/Edge user agent into the library. (One of the benefits of PR #48 is that configuring a user agent only needs to be done once, not once for each line of code doing .get().) AEMO might not like this though. Should we instead try to contact AEMO to ask them to allow traffic to this file, or put this data into the normal MMS dataset on nemweb?

Workaround for now

Manually open this page in a browser, and download the file "NEM Registration and Exemption List" to Registration and Exemption List.xls (not xlsx) in the data cache. Then when the code runs, it sees the file is there and won't attempt to re-download it.