See documentation for the following market: PJM, ISO-NE, MISO, and NYISO.
A how-to guide for using Excel to interface the PJM, NYISO, ISO-NE, and MISO energy markets.
At Pharos we have a philosophy against black box tools and dedicated software systems to send data to the various energy markets. Our software system Pharos AMS has been designed not to submit data but purely be a data capture system. When our clients need to submit data such as bids, offers or meter data we have developed tools for them that they own and can maintain to perform that function. If market rules, protocols or internal processes change adapting these tools is quick, inexpensive and can be accomplished by non-programmers. From a business continuity perspective these tools can be used anywhere without the need to be connected to a dedicated system.
This tutorial explains how to write macros in Microsoft Excel for Windows to submit properly formatted data to the various markets. It does not explain how to prepare the data. For that please refer to the various specifications produced by the respective markets. If you rather not develop these tools yourself we can assist you. Please email us at info@pharos-ei.com. We are happy to provide sample files and code to help you get started.
We have made this tutorial freely available. As a courtesy if you found this useful and used it in your applications just drop us a quick note. Also let us know if something is incorrect, can be done better or is simply not clear. If there are areas we have not covered and would like assistance let us know and we can add that to the documentation.
Microsoft Excel has limited ability to interface with the markets whose portals require certificates, usernames and passwords—at least in a way that a non-programmer can easily develop and support. What Microsoft Excel can easily do is prepare a properly formatted file for submittal to the market and automate everything around that submittal.
The process is simple. Excel is used to generate a csv or xml file that contains the data to be submitted. Excel then calls an external "helper application" that can properly send that data to the market using the appropriate certificate/username and password combination.
The helper application we use is called cURL. It is a very powerful opensource command line tool to transfer data to and from websites. It comes standard as part of the UNIX operating systems (Apple’s Macintosh OS X are based on a variant of UNIX). It is not part of Windows and therefore must be installed on Windows machines. To download cURL go to: http://curl.haxx.se/download.html. The site also explains how to use cURL.
cURL does not require any special installers. It is a lightweight application that can sit anywhere on your computer and should get past most IT policies. We will often keep the cURL application in the same folder as the Excel workbook.
To test the most basic functionality of cURL on a windows machine open up a terminal window by launching cmd.exe. Locate the cURL application and type curl followed by the website you want to download. On a unix based machine you can just open a terminal window type the command. So to download google’s website into the window you would type:
curl www.google.com
The result would return the html of the google homepage in the terminal window.
cURL is much more powerful that that. It allows you to save the results and also pass information such as files and certificates. This tutorial is focused on how to have Excel interface with cURL to upload and download a properly formatted file to the various energy markets from within Excel. The parameters of cURL will be different based on the market and how the market’s are to be interfaced.
If we were to expand on the cURL example above and have Excel call cURL from within a macro and return the result to a file we will need to use the Shell function. The shell function can run any executable (.exe) file as if it were run from the windows command line.
The shell function returns a value when run and as such we will need to make it equal to some variable. The function has the following form:
ShellValue = Shell(pathname \[,windowstyle\])
The pathname is the full path to the cURL application along with all of its parameters. The window style basically tells it how to display the terminal window. We prefer setting the style to 1 since it leaves the window open while it runs and makes it easier to see if the job is running, has an issue or has completed.
Here is sample VBA code to download google’s webpage and save the output to a file called cURLoutput.html. It assumes cURL is stored in the folder called "cURL Folder" in the root of the C drive. The variable shellcommand is the path to the cURL executable along with the parameters telling it what to do.
shellcommand = """C:\cURL Folder\curl"" www.google.com -o ""C:\cURL Folder\cURLoutput.html"""
Shellvalue = Shell(shellcommand, 1)
Note the use of the double quotes. The folder called "cURL Folder" has a space in the name. A space is used to separate the parameters of a command. To not have that space delineate a parameter and treat it as a space the path will need to be bounded by quotes. Put another way "cURL Folder" is one item where as cURL Folder is calling cURL and passing it the parameter Folder.
Since a quotation mark has meaning in VBA we will need to tell Excel that the quotation mark is part of the text. To do that we need to bound the quotation mark in its own quotes. Hence why we see three quotation marks at the beginning.
If this were to be used at the command line and not within Excel the command would look like this:
"C:\cURL Folder\curl" www.google.com -o "C:\cURL Folder\cURLoutput.html"
As you can see we do not actually need Excel to call these commands. Please note that in order to use the following cURL commands in the rest of this document within Excel the quotation marks will need to be properly handled. We recommend testing first in the command line and then converting it to something Excel can pass.
The "-o" parameter defines the output file that captures the response returned from cURL. The cURL manual explains this and all other parameters. We recommend always capturing the output file even when submitting as it contains important information from the market’s website such as whether the submittal was successful or why it was unsuccessful.
As a convention when we design Excel applications we try not to hard code commands like this and prefer to have them referenced from the spreadsheet. This way they can be dynamically produced. In this line of code the shellcommand reference cells A1 of the Sheet1 tab:
shellcommand = Sheets("Sheet1").Cells(1, ‘A").Value
where the A1 contains the formula:
="""C:\cURL Folder\curl"" www.google.com -o ""C:\cURL Folder\cURLoutput.html"""
Cell A1 could reference other cells to build up a more dynamic command line.
The rest of the manual will not be providing visual basic specific commands. It will be focused on the proper cURL commands and certificate handling when necessitated. In theory Excel is not required. A simple text editor can create the necessary files and cURL can be called from the command line. Excel is purely a facilitator to automate and simplify the process.
In some markets a certificate is required to interface with the market. In order for cURL to use the certificate it has to be converted into something that cURL can use.
An application called openssl must be installed on your computer to perform the conversion. Openssl is part of any Unix based operating system like Mac OS or any of the Linux distributions. For Windows the application can be found here:
http://slproweb.com/products/Win32OpenSSL.html
Install the latest version. It can be Light version of the application.
Some markets have a preference for how the certificates must be converted. Those specifications are provided in their respective sections below.
This manual will not tell you how to properly format a file for upload to the market. For that we direct you to the following manuals for each market. We are not providing links since the links can change. This is what you are to search for:
PJM
external-interface-specification-guide.pdf
Markets Gateway Quickstart Guide.pdf
pjm browserless authentication-guide.pdf
ISO-NE
eMarket Data Exchange Specification.pdf
MISO
DART Market User Interface.pdf
Market Participants User’s Guide (MPUG).pdf
All authentication with PJM’s servers is done with a "token." Before submitting to PJM a token has to be generated. That is accomplished by supplying their servers with a username and password. In return they will supply a token that is active for 24 hours after which a new token must be generated. Starting in 2021 PJM is moving to PKI-Based Authentication which requires the additional use of a NAESB certificate. A token request must be made with the username, password and the certificate assigned to the username. Note a NAESB certificate can only be assigned to one user. It cannot be shared among users. Fortunately PJM allows for a user account to have access to more than one market participant. Certificates purchased for NYISO or MISO can be simultaneously be used in PJM without the need to purchase additional certificates.
cURL needs to have the certificate broken out into its three components. To convert the openssl application detailed above will need to be used.
Procedure:
1. From your browser export the certificate to a .pfx file. Make sure the option to export the private key and include all certificates in the certification path if possible are selected. Add a password to the file. To make things easy save it to the same folder as openssl and do not use spaces in the filename.
2. In windows open a terminal window by going to the start menu and typing 'cmd'. it should be your first and only option. In unix environments skip to the next step.
3. At the command prompt navigate to where you saved the certificate.
4. Call the following openssl commands
openssl pkcs12 -in "path to Certificate.pfx" -passin pass:password -clcerts -nokeys -out "path to Certificate.client.crt"
Upload this version to PJM by applying it to the appropriate user account.
Next run these commands to create the other components in order to authenticate to get a token.
openssl pkcs12 -in "path to Certificate.pfx" -passin pass:password -nodes -nocerts -out "path to Certificate.key.pem"
Where
Path to Certificate.pfx is the full path and name of the certificate exported in step 1. Path to Certificate.*.pem is the path to the converted certificate components. Password is the password the certificate was given when exported.
To get this token you will need to pass this command to curl. It will create a file called token.txt in the cURL folder.
With PKI-Based Authentication the token request must be made with the NAESB certificate that was converted above. It would look like this:
curl \
--request POST \
--key Certificate.key.pem \
--cert Certificate.client.crt \
--header "X-OpenAM-Username:usernamehere" \
--header "X-OpenAM-Password:passwordhere" \
--header "Content-Type:application/json"\
--data "" \
-k \
-o "token.txt" \
"https://sso.pjm.com/access/authenticate/pjmauthcert"
For the sandbox you would authenticate here:
https://ssotrain.pjm.com/access/authenticate/pjmauthtraincert
Note if the password contains an exclamation point it may have to be escaped with a `\1.
So password1! Would be password1\!
The result will be a text file called token.txt that looks something like this:
{"tokenId":"AQIC5wM2LY4SfcyaPSIVgeuH\_akwP\_8DCKOabcdefjkjYXc.\*AAJTSQACMDIAAlNLABMxMzgxNDMwMTYzMjAxOTAyOTY0AAJTMQACMDM.\*","successUrl":"/openam/console"}
The actual token part is this:
AQIC5wM2LY4SfcyaPSIVgeuH\_akwP\_8DCKOabcdefjkjYXc.\*AAJTSQACMDIAAlNLABMxMzgxNDMwMTYzMjAxOTAyOTY0AAJTMQACMDM.\*
The next step will need to extract the token from that file.
If this is being performed in Excel. One could import the file.
Here is a sample Excel formula that will extract it if the value is in cell A1=
=MID(A1,FIND(":",A1)+2,FIND(",",A1)-1-(FIND(":",A1)+2))
The token can be used for the different PJM applications as they are assigned to the specific username and password combination for 24 hours.
There is a way to request a token with a certificate in Excel. It is more complex and less easy for an average Excel user to modify when certificates need to be updated. Using cURL makes it more explicit.
Submitting to Power Meter also uses cURL. Here is an example command.
curl \
--request PUT \
--header "Cookie: pjmauth=AQIC5wM2LY4SfcyaPSIVgeuH\_akwP\_8DCKOabcdefjkjYXc.\*AAJTSQACMDIAAlNLABMxMzgxNDMwMTYzMjAxOTAyOTY0AAJTMQACMDM.\*" \
--header "Content-Type: application/xml" \
--data-binary "@C:\cURL Folder\uploadfile.xml" \
-k \
-o "C:\cURL Folder\response.xml" \
https://powermeter.pjm.com/powermeter/rest/secure/upload/xml/dailysubmission/uploadfile.xml
Note that we are using the token from from before. It starts after pjmauth=. If we were submitting to the sandbox we would use pjmauthtrain=
The file we are uploading is uploadfile.xml located at @C:\cURL Folder\uploadfile.xml. That is an at symbol at the beginning of the path. Be sure to add the filename after the https:// link. So uploadfile.xml or whatever the filename you are using should be listed twice in the command. The response is saved to the file response.xml in the "cURL folder." The response file can provide valuable insight into why something is not being submitted properly.
To upload to the sandbox swap https://powermeter.pjm.com/powermeter/ for https://powermetertrain.pjm.com/powermeter/
All of the components of the cURL command can be formulaically created within Excel. So the token, path to files, file names can all be referenced easily in Excel.
Once data has been submitted we recommend downloading the data to confirm what was submitted matches what was in the Excel tool. To do that we would pass it this command:
curl \
--request GET \
--header "Cookie: pjmauth=AQIC5wM2LY4SfcyaPSIVgeuH\_akwP\_8DCKOabcdefjkjYXc.\*AAJTSQACMDIAAlNLABMxMzgxNDMwMTYzMjAxOTAyOTY0AAJTMQACMDM.\*" \
-k \
-o "C:\cURL Folder\downloadfile.csv" \
"https://powermeter.pjm.com/powermeter/rest/secure/download/csv/dailysubmission?start=12-01-2015&stop=12-01-2015"
If submitting using a SUMA account be sure to pass this header:
–header "Suma-Account: SUMAAccount"
Note the start and end dates in the format mm-dd-yyyy. Here we are downloading just the one day that was submitted for 12/1/15. This date was embedded in the submittal.
The file format requested of the download is csv. Note the instance of the csv in the powermeter link as well as the filename.
InSchedule is very similar to Power Meter. The only difference are the
Production Server: https://insched.pjm.com/inschedule/
Sandbox Server: https://inschedtrain.pjm.com/inschedule/
Submission:
<<To Be Added>>
Note there is a question mark in the queries. As a result be sure to put quotes around the endpoint.
Contracts Query:
"https://insched.pjm.com/inschedule/rest/secure/download/csv/contracts?start=MM-DD-YYYY&stop=MM-DD-YYYY"
Schedules Query:
"https://insched.pjm.com/inschedule/rest/secure/download/csv/schedules?start=MM-DD-YYYY&stop=MM-DD-YYYY"
Companies Query:
"https://insched.pjm.com/inschedule/rest/secure/download/csv/companies?start=MM-DD-YYYY&stop=MM-DD-YYYY"
Reconciliations Query:
"https://insched.pjm.com/inschedule/rest/secure/download/csv/reconciliations?start=MM-DD-YYYY&stop=MM-DD-YYYY"
Submitting to Markets Gateway also uses cURL. Here is an example command.
curl \
--request POST \
--header "Cookie: pjmauth=AQIC5wM2LY4SfcyaPSIVgeuH\_akwP\_8DCKOabcdefjkjYXc.\*AAJTSQACMDIAAlNLABMxMzgxNDMwMTYzMjAxOTAyOTY0AAJTMQACMDM.\*" \
--header "Content-Type: application/xml" \
--data-binary "@C:\cURL Folder\uploadfile.xml" https://marketsgateway.pjm.com/marketsgateway/xml/query/ -k -o "C:\cURL Folder\response.xml"
Note that we are using the token from before. It starts after pjmauth=. If we were submitting to the sandbox we would use pjmauthtrain=
The file we are uploading is uploadfile.xml located at @C:\cURL Folder\uploadfile.xml. That is an at symbol at the beginning of the path. Be sure to add the filename after the https:// link. So uploadfile.xml or whatever the filename you are using should be listed twice in the command. The response is saved to the file response.xml in the "cURL folder." The response file can provide valuable insight into why something is not being submitted properly.
To upload to the sandbox swap https://marketsgateway.pjm.com/marketsgateway/xml/query for https://marketsgatewaytrain.pjm.com/marketsgateway/xml/query
All of the components of the cURL command can be generated in Excel and called from Excel using the shell command. In our tools the token, path to files, file names are all referenced from within a workbook’s cells.
We use cURL to get the token. Once the token is received then we can go directly to PJM. No username or password is required. To interface with PJM directly without cURL you can use the following Visual Basic adapted from the example provided by PJM.
SubmitServer = "https://marketsgateway.pjm.com/marketsgateway/xml/"
‘ for sandbox use https://marketsgatewaytrain.pjm.com/marketsgateway/xml/
CookieType = "pjmauth"
‘for sandbox use pjmauthtrain
Method = "query"
‘to submit use "submit" as the method.
Set oWinhttp = New WinHttpRequest
With oWinhttp
Call .SetTimeouts(300000, 300000, 300000, 300000)
Call .Open("POST", SubmitServer + Method)
Call .SetRequestHeader("Cookie", CookieType + "=" + tokenStr)
'Call .SetRequestHeader("XParticipantName", SUMAAccount)
Call .SetRequestHeader("Content-Type", "text/xml")
Call .SetRequestHeader("Content-Length", Len(xmlSubmittal))
Call .Send(xmlSubmittal)
'Wait for the response asynchronously.
Call .WaitForResponse
'Display the response text. You could also store to a variable
result = .ResponseText
Call .Abort
End With
Where xmlSubmittal is a properly formatted xml file to send to PJM.
tokenStr is the token that was captured earlier.
Note line:
Call .Open("POST", SubmitServer + Method)
This was done to be more generic. This could have been more explicit and written as
Call .Open("POST", "[https://marketsgateway.pjm.com/marketsgateway/xml/query](https://www.google.com/url?q=https://marketsgateway.pjm.com/marketsgateway/xml/query&sa=D&source=editors&ust=1658947204759897&usg=AOvVaw1dIX1304LkePgsm0r5aBws)")
to query or
Call .Open("POST", "[https://marketsgateway.pjm.com/marketsgateway/xml/submit](https://www.google.com/url?q=https://marketsgateway.pjm.com/marketsgateway/xml/query&sa=D&source=editors&ust=1658947204760767&usg=AOvVaw1J9Uumy_kg_mboExNvfq6g)")
to submit.
With PJM moving to require certificates it may be advantageous to use a Single User Multi-Account (SUMA) account since a certificate can only be mapped to a single user. With a SUMA account you can access many market participants saving the purchase of multiple certificates. To use a SUMA account the only change is to add an extra header in the submission. In the VBA example above that call is commented out show here:
Call .SetRequestHeader("XParticipantName", SUMAAccount)
Where SUMAccount would be the market participant’s shortname.
Submitting via cURL you would add the header
--header "XParticipantName: SUAMAccount"
PJM provides an HTTP GET call to be able to download any MSRS report. That is equivalent to saying every report has its own unique web address. Since Excel can open a file that has a web address it means it can also open these reports. When we build settlement applications for PJM we have Excel run a macro that downloads, copies and pastes the content of each report into the application.
In order to make any of these requests you will need to authenticate with two factor authentication using a username and password and if set up using PKI Certificates a certificate.
The browserless URL documentation can be found on the PJM MSRS website: http://www.pjm.com/markets-and-operations/billing-settlements-and-credit/msrs-reports-documentation.aspx
Updated for 5 minute settlements here:
Note some reports must be downloaded one per day versus a month or range at a time.
Please be aware of the usage guidelines:
http://www.pjm.com/~/media/markets-ops/settlements/msrs/20140311-msrs-usage-guidelines.ashx
The MSRS Report Dictionary provides more specific details on each report:
http://www.pjm.com/~/media/markets-ops/settlements/msrs/msrs-report-dictionary.ashx
To request a report you will need to create a properly formatted URL.
The basic link takes this form:
"https://msrsapp.pjm.com/msrs/rest/secure/download/reports?" followed by a set of parameters. Each parameter is separated by an ampersand. The order of the parameters does not make a difference.
For a list of reports pass this parameter "list=true" or if XML format is required "listxml=true"
For example:
https://msrsapp.pjm.com/msrs/rest/secure/download/reports?list=true
To pull a specific report you would pass these parameters:
https://msrsapp.pjm.com/msrs/rest/secure/download/reports\=<REPORT\_SHORT\_NAME>&version=<VERSION\_CODE>&format=<FORMAT\_CODE>&start=<START\_DATE>&stop=<END\_DATE>&org=<OrgShortName>
Where:
<REPORT_SHORT_NAME> is the report short name, lowercase with spaces removed and any ampersands replaced with %26
To create a valid list of report short names use the get report list weblink. Then remove all spaces, make the list all lowercase and then search for "&" and replace with "%26" (ignoring the quotes). The report:
"DR & ILR Compliance Penalty Ch and Cr" becomes
"dr%26ilrcompliancepenaltychandcr"
<START_DATE> and <END_DATE> must be in the full mm/dd/yyyy format like 01/01/2016. Each report has a limit on how many days can be requested at a time. The MSRS Report Dictionary lists the number of days. The majority of reports allow the ability to download 31 days or a full month at a time.
<VERSION_CODE> is
If L is selected the most recent version will be displayed. If one wants to see how an invoice changes over time select A for All Billed and it will return the history. For most applications you would want to select L for latest.
<FORMAT_CODE> can be one of the following:
For SUMA accounts you would need to pass the organization’s shortname.
If importing into Excel CSV is recommended. Importing xml requires a few extra steps that may not always work.
Here is an example to grab the DR & ILR Compliance Penalty Ch and Cr report in CSV format for the month of January 2016:
https://msrsapp.pjm.com/msrs/rest/secure/download/reports?report=dr%26ilrcompliancepenaltychandcr&version=L&format=C&start=01/01/2016&stop=01/31/2016
In order to authenticate there is a two step process. Follow the PJM Token Request procedure above to receive a token. With that token you can then pass the token in place of a username and password to PJM.
Below is a sample request using cURL:
curl \
--request GET \
--header "Cookie: pjmauth=XXXXXXXXXXXXXX.ZZZZZZZZZZZZZZZ" \
--header "Content-Type:text/csv" \
"https://msrsapp.pjm.com/msrs/rest/secure/download/reports?version=L&shortName=weeklybillingstatement-csvandxml&stop=01-07-2020&format=C&start=01-01-2020"
If using cURL, note that if your password has an exclamation point you may have to escape the ! with a \. So say your password is Password1! then you would have to use Password1\!. An exclamation point has a specific meaning when used at the command line.
To do this in Excel you will have to have to have your VB code use basic authentication. There are plenty of examples of it on the internet.
Here is a code snippet we use in our Excel tools. We pass the function GetPJMMSRS the MSRS link and the token and returns the CSV file
Function GetPJMMSRS(MSRSUrl As String, Token As String) As Boolean
'Dim objHttp As New MSXML2.XMLHTTP
Dim objHttp As Object
Set objHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim strURL As String
Dim strUserName As String
Dim strPassword As String
Dim filetosave As String
filetosave = "MSRSTemporyFile.csv"
objHttp.Open "GET", MSRSUrl, False
objHttp.setRequestHeader "Cookie", "pjmauth=" + Token
objHttp.setRequestHeader "Content-Type", "text/csv"
objHttp.Send
'"Nothing"
Dim strResponseReceived As String
strResponseReceived = objHttp.responseText
fnum = FreeFile()
Open filetosave For Output As fnum
Print #fnum, strResponseReceived
Close #fnum
Workbooks.Open filetosave
'Kill filetosave '--deletes the file
End Function
Email us at info@pharos-ei.com for a free functioning Excel workbook with macros exposed that will capture all MSRS reports for a given month and will also cycle through reports that must be downloaded one day at a time.
ISO-NE requires authentication with a certificate. cURL has no problem handling this. The first step is to prepare the certificate for use by cURL.
cURL needs to have the certificate broken out into its three components. To convert the openssl application detailed above will need to be used.
Procedure:
openssl pkcs12 -in "path to Certificate.pfx" -passin pass:password -cacerts -nokeys -out "path to Certificate.ca.pem"
openssl pkcs12 -in "path to Certificate.pfx" -passin pass:password -clcerts -nokeys -out "path to Certificate.client.pem"
openssl pkcs12 -in "path to Certificate.pfx" -passin pass:password -nodes -nocerts -out "path to Certificate.key.pem"
Where
These three commands will convert the pfx file into three pem component files with the extension *.ca.pem, *.client.pem and *.key.pem. All three components are required for submitting to the market.
To send a file to ISO-NE you would pass it this command:
curl -k \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
-d @"Path to upload file" \
https://smd.iso-ne.com/emktmui/webservices/emkt \
-o "path to response file"
To submit to the sandbox use https://sandboxsmd.iso-ne.com/emktmui/webservices/emkt instead of https://smd.iso-ne.com/emktmui/webservices/emkt
The Path to upload file contains the xml data that needs to be submitted. It can be a query or it can be posting data depending on the contents of the file.
Uploading a query for a wind forecast uses the same command as the SMD Bids & Offers. The one exception are the endpoints. They are not public. To get them contact ISO-NE Customer Support at custserv@iso-ne.com or call (413) 540-4220.
Uploading files to ISO-NE requires a three step process: log in and get a cookie, set up the session and then upload the file. See the appropriate ISO-NE manuals for how to structure a file to upload.
Production endpoint: https://smd.iso-ne.com/sms_oper_contract/main
Sandbox endpoint: https://sandboxsmd.iso-ne.com/sandbox_contract/main
Use this command to get the cookie and save the cookie to the file cookie.txt
curl -i 'https://smd.iso-ne.com/sms\_oper\_contract/main' \
-X GET \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
-c cookie.txt
Use this command to set up the session for file upload mode by using the cookie in cookie.txt
curl 'https://smd.iso-ne.com/sms\_oper\_contract/main' \
--data 'servlet\_action=FR\_CONTRACT\_FILE' \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
-b cookie.txt
Use this command to upload the file with the submittal using the cookie in cookie.txt
curl 'https://smd.iso-ne.com/sms\_oper\_contract/main' \
-L \
-X POST \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
--form "file=@uploadfile.xml" \
-o IBTUploadResults.html \
-b cookie.txt
Here the response from ISO-NE is exported as an html file which can be further processed for confirmation of an upload or make it easy to open in a browser to see if the upload was successful.
Uploading files to ISO-NE requires a three step process: log in and get a cookie, set up the session and then upload the file. See the appropriate ISO-NE manuals for how to structure a file to upload.
Production endpoints:
Step 1: https://smd.iso-ne.com/sms_oper_lfrassetassign/main.do
Step 2: https://smd.iso-ne.com/sms_oper_lfrassetassign/lfrFileUpload.jsp
Step 3: https://smd.iso-ne.com/sms_oper_lfrassetassign/fileUpload.do
Sandbox endpoints:
Step 1: https://sandboxsmd.iso-ne.com/sandbox_lfrassetassign/main.do
Step 2: https://sandboxsmd.iso-ne.com/sandbox_lfrassetassign/lfrFileUpload.jsp
Step 3: https://sandboxsmd.iso-ne.com/sandbox_lfrassetassign/fileUpload.do
Use this command to get the cookie and save the cookie to the file cookie.txt
curl -i ' https://sandboxsmd.iso-ne.com/sandbox\_lfrassetassign/main.do' \
-X GET \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
-c cookie.txt
Use this command to set up the session for file upload mode by using the cookie in cookie.txt
curl -i ' https://sandboxsmd.iso-ne.com/sandbox\_lfrassetassign/lfrFileUpload.jsp' \
-X GET \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
-b cookie.tx
Use this command to upload the file using the cookie in cookie.txt.
curl 'https://sandboxsmd.iso-ne.com/sandbox\_lfrassetassign/fileUpload.do' \
-L \
-i \
-X POST \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" \
--form browseFile=@uploadfile.xml \
--form action=FILEUPLOAD \
--form submitFile=Submit \
-b cookie.txt \
-o FRAuploadresponse.htm
The upload file can be one of two formats as described in the ISO-NE Manuals. It can be an xml or a text file format. NOTE we are having issues submitting xml using this method. We are getting an invalid CustomerID error even though the customerID is fine and it submits fine using the text file format.
In this example the response from ISO-NE is exported as an html file which can be further processed for confirmation of an upload or make it easy to open in a browser to see if the upload was successful.
Pharos has an Excel based settlements reporting application that utilizes the MISFTP csv settlement reports. It performs the calculation of applying the charges and credits assinged at the portfolio, regional or zonal level to individual units using the same methodology as the ISO. The application uses a flat file database of the original csv files stored on a local or networked drive. The below windows based batch script automates the synchronization of the FTP site with the local drive, the decompression of the zip files into CSV form and then the syncing, if necessary to another location on a drive.
The script requires two helper applications: 7zip and WinSCP.
Once installed all that is requires is updating the script to point to the locations of the helper applications, where two store the files and the ISO-NE MISFTP username and password.
@echo off
:: Application Locations
set zip="C:\Program Files\7-Zip\7z.exe"
set WinSCP="C:\Program Files (x86)\WinSCP\WinSCP.com"
:: FTP Details
set username="username"
set password="password"
:: FileLocations
set MISFTPFilesLocation="C:\PATH TO MISFILES FOLDER"
set scriptLocation="C:\PATH TO WHERE THIS SCRIPT IS LOCATED FOR PUBLISHING LOGS"
set RoboDestination="C:\PATH TO LOCATION COPYING CSV FILES TO ALTERNATIVE LOCATION"
:: Synchronizing script using WINSCP
%WinSCP% /command "open sftp://%username%:%password%@misftp.iso-ne.com/" "option transfer binary" "synchronize local "%MISFTPFilesLocation%"" "exit" > %scriptLocation%\WinSCPlog.log
:: Unzip the files and only unzip those files not already decompressed.
FOR /F "tokens=*" %%a in ('dir /a-d /s /b %MISFTPFilesLocation%\*.gz') do %zip% e "%%a" -o"%%~dpa" -aos > %scriptLocation%\UnzipLog.log
:: Copy files to different location
:: Copy All
::robocopy %MISFTPFilesLocation% %RoboDestination% *.CSV /e /r:1 /w:1 > %scriptLocation%\Robocopy.log
:: Copy By Directory if only a subset is desired
::DEL %scriptLocation%\Robocopy.log
::robocopy %MISFTPFilesLocation%\BL\ %RoboDestination%\BL *.CSV /e /r:1 /w:1 >> %scriptLocation%\Robocopy.log
::robocopy %MISFTPFilesLocation%\SD\ %RoboDestination%\SD *.CSV /e /r:1 /w:1 >> %scriptLocation%\Robocopy.log
::robocopy %MISFTPFilesLocation%\SR\ %RoboDestination%\SR *.CSV /e /r:1 /w:1 >> %scriptLocation%\Robocopy.log
::robocopy %MISFTPFilesLocation%\TR\ %RoboDestination%\TR *.CSV /e /r:1 /w:1 >> %scriptLocation%\Robocopy.log
ISO-NE directs you to use their web services for public data. To use you will need to sign up for ISO Express and select the ISO Data Feeds box during registration. They provide a list of all endpoints on their documentation page. To query you will need to use basic authentication with your ISO Express username and password. Typically your username is your email address.
Here is an example of using cURL to request the seven day forecast published for a specific date and saving it to a file called forecast:
curl -u 'username:password' 'https://webservices.iso-ne.com/api/v1.1/sevendayforecast/day/20201015' -o 7dayforecast.xml
Note with very few exceptions the ISO-NE web services returns only XML or JSON.
To achieve the same result in VBA you could use this code that utilitzes the Base64Encode function above.
Sub DownloadISONEWedServices()
'Dim objHttp As New MSXML2.XMLHTTP
Dim objHttp As Object
Set objHttp = CreateObject("Microsoft.XMLHTTP")
Dim strURL As String
Dim filetosave As String
Dim strResponseReceived As String
filetosave = "7dayforecast.xml"
strURL = "https://webservices.iso-ne.com/api/v1.1/sevendayforecast/day/20201015"
objHttp.Open "GET", strURL, False
objHttp.setRequestHeader "Authorization", "Basic " & Base64Encode(Username + ":" + Password)
objHttp.Send
strResponseReceived = objHttp.responseText
fnum = FreeFile()
Open filetosave For Output As fnum
Print #fnum, strResponseReceived
Close #fnum
Workbooks.Open filetosave
End Sub
The MISO certificate preparation procedure is exactly the same as ISO-NE.
The cURL commands are similar to ISO-NE except there are different commands when querying for data and submitting.
Query:
curl \
-H "SOAPAction: /dateor/xml/query" \
-H "Content-Type: text/xml" \
-k \
--key "path to CertificateFile.key.pem " \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" -d @"Path to upload file" https://markets.midwestiso.org/darteor/xml/query \
-o "path to response file"
Submit:
curl \
-H "SOAPAction: /dateor/xml/submit" \
-H "Content-Type: text/xml" \
-k \
--key "path to CertificateFile.key.pem" \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.ca.pem" -d @"Path to upload file" \
https://markets.midwestiso.org/darteor/xml/submit \
-o "path to response file"
When testing in the sandbox you will need to change
https://markets.midwestiso.org/ to https://cce.midwestiso.org/
Submitting meter data is no different except you are submitting to:
https://markets.midwestiso.org/PI/MeterDataUpload/
Here is the example cURL command:
curl \
-H "Content-Type: text/xml" \
-k \
--key "path to CertificateFile.key.pem \
--cert "path to CertificateFile.client.pem" \
--cacert "path to CertificateFile.cacert.pem" -d @@"Path to upload file" \
https://markets.midwestiso.org/PI/MeterDataUpload/ \
-o "path to response file"
To submit bids and awards to the NYISO a text file must be uploaded through the NYISO bidpost portal. Just like in the other markets we can use the cURL application to manage that upload. The username and password are embedded in the upload file.
First the certificates must be converted into a form that both cURL and the NYISO will accept. With an exported certificate file. Using openssl we will need to convert the .pfx file to a .pem file by issuing this command:
openssl pkcs12 -in cert.pfx -out cert.pem -nodes
Where cert is the certificate name.
What is also required is the Root NYISO certificate issued by the Certificate Manager. Export the certificate then convert using this command;
openssl x509 -inform DES -in CertificateManager.cer -out CertificateManager.pem -text
Then after a file with the submittal is created we will want to pass that to the NYISO portal using cURL. To do that we would use the following commands:
Bidpost:
curl \
-k \
--form uploadfile=@"Path to NYISOuploadfile.txt" \
--cert cert.pem \
--form press=SUBMIT \
-o "Path to response file.csv" \
https://bidpost2.nyiso.com/cgi-bin/main.bin
JESS:
curl \
-k \
--form uploadfile=@"Path to NYISOuploadfile.txt" \
--cert cert.pem \
https://jess.nyiso.com/updown/updown \
--form press=SUBMIT \
-o "Path to response file.csv"
where the "Path to NYISOuploadfile.txt" file is the file that will be submitted to the NYISO and the "Path to response file.csv" will be where the response to the submittal will be stored.
cURL can also be used to retrieve DSS files. The first step is to log into DSS with a certificate and a DSS username and password.
curl \
-k \
--cert cert.pem \
-c cookie.txt \
"https://dss.nyiso.com/dss/login.jsp?user=USERNAME&pass=PASSWORD&automated=2"
The -c cookie.txt will save the session for use later. This command will return a list of available files which have three columns:
FILE_ID,FILE_NAME,PUBLISHED_TIME
To download an individual file use the FILE_ID and FILE_NAME from the returned list of files
curl \
-k \
--cert cert.pem \
-b cookie.txt \
-o $FILE_NAME".csv" \
"https://dss.nyiso.com/dss/docViewAGN.jsp?RepoType=I&ID=$FILE_ID&DocName=$FILE_NAME&entry=&DocType=csv"
When done log out.
curl \
-k \
--cert cert.pem \
-b cookie.txt \
"https://dss.nyiso.com/dss/scripts/dssADDAutomationLogout.jsp"