||Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||

Acquiring Data on Merchant Electricity and Gas Prices from Around the World

For many years I have wanted to collect electricity and gas price data from different markets around the world and publish it in a central place. I want this so that you can just press a button in some excel files and get the data for a whole lot of different markets. Then you can compare electricity price volatility in different markets; evaluate the relationship between gas and electricity prices around the world; compare the level of electricity price in common currencies in different countries and regions; compute the economics of different types of new plants such as solar, wind and NGCC in different markets and many other things. You can also study fascinating cases including the California crisis, the crash in UK prices leading to bankruptcies of every plant that was purely merchant; effects of renewable energy on merchant prices in Germany; transmission constraints; effects of hydro on markets and many other issues.

To make the data comparable across the world, I have converted prices into USD per MWH so you can evaluate relative price levels in different places. (If nominal prices in local currency remain constant, but there is a devaluation in the currency, then the prices have effectively declined.) Once you have the electricity market price data you can compute the profitability of different plants if you download commodity energy prices that are often reported in USD. For example, you can sum the value that a solar project would earn in different markets through some assumptions about the timing of electricity production during the day and during the season. When you sum this over the year, you can derive the USD per kW per year. This amount can then be compared to the fixed cost per kW per year (see the LCOE page). I have also tried to compare the prices of electricity to the price of gas, oil or coal. For a dispatchable thermal plant this is essential and it demonstrates that around the world, the prices of electricity are related to the price of natural gas.

One of the best things about comparing detailed historic data from different markets is the wonderful case studies that can be evaluated. These case studies include: Price spikes and valuation of capacity in Australia; the California crisis with outages, hydro changes and market power; price spikes in the Midwest U.S. that prompted overbuild of peaking capacity; effects of capacity increases in U.K. and New England and the long-lasting effects of surplus capacity; effects of transmission constraints and renewable capacity in the Northwest U.S.; declines in electricity prices driven by gas prices and renewable energy causing one of the largest bankruptcies in history in Texas; changes in prices in Philippines and Turkey that move prices closer to worldwide levels; and prices in Nordpool that are less related to natural gas prices because of hydro energy.

Different markets have different challenges in terms of retrieving data. The UK has been the worst and some markets such as Nordpool are very transparent (luckily, you can now get UK prices from Nordpool). For Nordpool, you can just copy urls to a spreadsheet and use the WORKBOOKS.OPEN statement to automate the process. Australia has transparent data and some markets in the U.S. are better than others. In other markets such as Turkey and the Philippines you have to use a more manual approach. On this page I include files that have historic electricity and gas prices as well as a description of how to update the data. You should be able to update the data yourself if my instructions are anything close to reasonable. But if this is difficult, you can just use the files and look at the last update.

Merchant Prices and Analysis in Turkey

The files for electricity prices in each region combine a historic data set with spreadsheet processes that permit updating in an easy manner as possible. Prices for just about every market also tell a story and represent a case study. I looked at Turkey prices a few years ago when I was working in Armenia (there is no transmission connection). Turkey prices adjusted for both the exchange rate and for changes in gas price (through converting prices to implied heat rates and spark spreads) were much higher than Europe and other places in the world. I also ran into people making investments in Turkey who were assuming prices would remain constant or even increase. The clear question was whether these prices would continue at high levels relative to gas prices, coal prices and in absolute terms. This conversion to a common currency and examination of prices in the context of gas and coal prices is similar for all of the regions presented below.

I have finally updated the turkey prices and included instructions on how you can regularly update prices and keep your analysis current. Begining hourly prices are recorded from sometime in spring 2009. You can download the prices on an hour by hour basis using the link below which provides hour by hour prices in TKL. But it is a pain to download the historic prices because you cannot download the prices all at once and you cannot get a URL to download the prices. After you compile the hourly prices by downloading separate files you can use the AVERAGEIF or the MATCH and INDEX you see that the prices seem pretty stable (see the video). But then I have tried to adjust all the prices on this page to USD. This is another pain because recent prices are not included in the FRED database and it is a pain to find a database with comprehensive history. So, I found a database that has day by day historic data that you can put together using the WORKBOOKS.OPEN function. After changing to USD, the story is completely different. As such, the case demonstrates the importance of adjusting prices to a reference currency. Once the prices are changed, you can see a close correlation to natural gas prices (I use the European prices from PINK data published by the world bank. After you make these adjustments the story completely changes. Now the prices come down over time and you can evaluate the relationship between electricity and gas prices. Note that the volume is low on this video and if you have headphones it may help to use them.


U.S. Electricity and Gas Prices, Spark Spreads and Implied Heat Rates

The U.S. has many electricity markets with published prices that have now been around for a long time. The prices can be used to study many case studies including: (1) sudden spikes in the Midwest U.S. followed by lower prices for many years; (2) the California power crisis of 2000-2001 with lower hydro, high plant outages; game playing in natural gas and electricity markets; (3) depressed prices in the NEPOOL market after new combined cycle capacity was built 2001-2002; (4) declining prices in ERCOT resulting in the TXU bankruptcy -- one of the largest bankruptcies in history after 2008; (5) transmission constraints and differential prices between MID-CON and California driven by renewables after 2010; and (6) increasing prices in PJM driven by retirement of coal capacity. I have tried to put all of the markets with gas and electricity prices into a single file so all of these issues can be studied.

I have compiled data on prices since various markets began to be published in the 1990's. You can go to the ISO New England, ISO New York and PJM websites and collect hourly data these days. You can also get day by day on-peak data from the EIA website (it does not have hourly detail and you cannot examine on-peak versus off-peak prices). The EIA website has different regions and with a little work you can get a history of prices and it includes a lot of data on comparative natural gas prices for the different regions. For electricity price to be relevant in valuing plants and evaluating new additions, natural gas coal and even oil prices may be relevant. The data demonstrates that U.S. natural gas market can be very different in different regions. For example, gas prices in New England sometimes reach very high levels because of limited natural gas pipeline capacity at something called the Algonquin hub. Not too far away in Pennsylvania, the gas prices can be very low because this is a region where shale gas is produced. The future prices in NEPOOL are therefore highly influenced by what will happen to pipeline capacity.

The philosophy of this and other files is to put together a file with a whole bunch of history that does not have to be modified. Then you can just update the file. This is the same with the gas and electricity file.

In creating merchant analysis prices I have generally included a file with historic prices and also a file with the ability to update prices. This is the same for other markets below including the WESM market in the Philippines, the UK market; the Australian markets and the Nordpool market. To put together electricity prices I first show how to get analysis from the EIA website which can be updated in a reasonably automated way. This EIA website can be used to get both natural gas and electricity prices by region. I have included a link to the website and files that read the electricity and gas data in an automated manner. To put the natural gas and electricity data together with history can be a little tricky. For example I had to arrange gas prices by region next to electricity prices from the same region is an important issue in computing spark spreads and in computing implied heat rates. So, here is the current process for automatically updating all of the natural gas and electricity data:

PART 1: Historic Electricity and Gas Prices for Different Regions

I have put together historic daily on-peak electricity prices and natural gas prices for different regions of the U.S. I have tried to go back as far into history as I possibly can for the different regions. I have prices beginning in 1996 for the California markets; back to 1997 for the Midwest markets; and back through 1999 for the New England region. I have put together data on prices that I have complied since various markets began to be published in the 1990's. You can go to the ISO New England, ISO New York and PJM websites and collect hourly data these days. You can also get day by day on-peak data from the EIA website (it does not have hourly detail and you cannot examine on-peak versus off-peak prices). To put on-peak prices together and update the database, you can do the following (it is pretty easy).

1. Go to the natural gas file and press the button to read the pricing at the regional hub pricing files (you only really need the files for the years after the year in the file) as well as the button to read the Henry Hub prices. You may have to add lines (copy rows) for the last lines of the various hubs. This should only take a minute or two.

2. Copy and paste the page of natural gas basis differentials to the electricity price file that is named Current EIA Format and Database -- the first file below. I have kept the electricity and gas files separate to make them easier to understand.

3. Press the button to read updated prices in the electricity price file. This should be automated and not pose problems and be finished in seconds.

Here is the EIA link that you can use to download both the electricity and gas data:


PART 2: Data from Independent System Operators Websites and US Case Studies

I have put some of my collections of other price history with hour by hour prices here. The EIA only has on-peak prices and if you want hourly data you need to go to the ISO websites that can be more painful. I have done some work in trying to put together data for the PJM website in the files below. This price history for California, PJM and New England has more refined on-peak and off-peak data and is not necessarily updated. The files demonstrate how to work with large hourly databases, how to compute spark spreads, market heat rates and simulated profit for alternative technologies.

The US regions have a lot of interesting case studies in Merchant pricing. The first and most dramatic case study is the California case study that began in the year 2000. People use this case study to demonstrate that merchant markets cannot work or that the merchant markets must be studied carefully. This case study involved questions of market power, true costs of electricity, incentives to build new capacity and demand response. Other case studies include the bankruptcies caused by prices in the northeast after 2001. More cases can be used to demonstrate how much gas affects the price of electricity in different regions and how the declining growth in electricity demand along with renewables has pushed down current and forward prices. This is why I have included forward price downloads from the CME. Forward prices that are automatically downloaded from the CME (there are a few problems with formatting data) are included in the first file below.

The price databases below go to the ISO websites and get data. They have off-peak and on-peak data and include much more detail than the EIA data. I am always in the process of updating the data.

Philippines Electricity Price Analysis

I have had the honor of going to the Philippines a lot to work with the MERALCO Training Academy. Each year when I go I try to update the Wholesale Electric Market (WESM) prices. It is a bit painful because you cannot read directly from one single source and use an automated download technique. So I have created a three step system to track historic price data, translate the price to USD and then put together a history of hourly merchant prices in USD/MWH. The reason I want USD per MWH is so that prices in the Philippines can be compared with prices around the world. You can also evaluate the profitability of solar and other resources against the market price. The Philippines experience is a little like Turkey. The prices have been higher than in other markets even after adjusting for high natural gas prices that are experienced in Asia. But as usual, the high prices prompt a lot of building (even though new coal plants have PPA agreements, the WESM provides a benchmark). With the new building, prices have been falling in the past few years. With this little comment, here are the steps to putting together a comprehensive database of the Philippines WESM prices:

STEP 1: Go to the WESM website and download month by month data in separate spreadsheet files. Your can keep them in a different folder or something. The URL for retrieving the these prices -- the Generalized Wholesale Average Price (GWAP - maybe the abbreviation is wrong) is at the following link. You use this link to supplement the first file listed below that is named WESM Download template.


STEP 2: The second step is to put updated prices from all of the files that you download in the comprehensive database. Of course you should open the database of historic files that is named "Basic Data Base" below and see where to start. So, to sum up, you begin with the Basic Data Base and then put the prices that you download into a file named the WESM Hourly Database.

STEP 3: To complete the process you need to put the Philippines Exchange Rate and Commodity price data into the WESM Hourly Database file. The commodity prices can be obtained from the World Bank Pink Data file. (You may need to check that the link is current because unfortunately the World Bank sometimes changes the url.) To get the Philippine to USD exchange rate you can use the Read PHP Exchange rates below or alternatively, the exchange rates are reported in file named Get Historic Exchange Rates that is shown next to the Turkish prices. (There used to be an easy way to do this from the Philippines government but for some reason it is no longer there.)

Once you have put together the together the file named WESM Hourly Database, you can do a lot of analysis. Some analysis of the economic value of different technologies in the file named WESM Value Analysis (which is a very large file because of analysis with hourly data). To manage this file you should first copy the data from the hourly database into the file. This file has a whole bunch of advanced techniques for graphing data with different time scales and do other analysis.

German Electricity Price and Natural Gas Analysis

I went to the German electricity market called eex.com to download prices and I discovered that I have to pay for the data!!! Of course I could not do this. This also really stinks because one of the basic objectives of merchant markets is to have transparent prices. Transparent prices can be used by consumers and can be used, transparent prices can be used to prevent bribes for high price contracts, transparent prices can be used by investors to decided what kind of plant to invest in, transparent prices can be used by consumers in deciding whether to lock into long-term contracts, transparent prices can be used by consumers to evaluate the benefits of demand response, and on and on and on. Anyway, you can at least download quarterly data which is ok for the kind of analysis that I often demonstrate and it shows some really interesting trends in spark spreads and in implied heat rates. So, I put the German price together with natural gas prices from the World Bank and with Exchange Rates from the FRED database. Once you do this you can quickly update all of the data by pressing a couple of buttons. You can download the file below and it should all work. The link for the free data (that is sometimes updated) and the general link is shown below:


UK Electricity and Gas Prices

As I was blabbering about above, transparency in prices is related in a fundamental way to the objectives of merchant markets. If the transparency for the German prices is bad, the UK process is so disgusting that it makes you scream. Go to Google and try to find UK electricity prices and gas prices. You get to a website named APX where you cannot even find how to buy the historic data. There is absolutely no reason it should not be transparent. They tell you to go to some kind of website were you are supposed to send an e-mail begging for the data. I did this and, after a few days I got a response that unless I was a company I could not get the data. Also you have to pay for it. How utterly unacceptable. Sorry about the moaning and, at any rate, if you look hard you can get some data from OFGEM and also from NORDPOOL.

I have tried to put data from various historic sources together with data from Nordpool. Despite the disgusting lack of data for the UK wholesale power markets I have put together a pretty comprehensive database below that comes from a couple of different sources of data that I have tried to carefully document and present in a transparent manner.The first file shows how I have put together the historic price data from various sources. The second file is a master database that displays trends in prices and implied heat rates that I find very interesting. The link to the Nordpool website that has U.K. data and verification of the Nordpool data against other sources is included in the files.

I use the database below to evaluate the crash that occurred in the UK after all of the new capacity and the change in the structure of the market. The crash was not due to NETA that changed the process for bidding and made sure that there was more demand response. I think it was due instead to breaking up the system and to the surplus capacity caused by the "dash to gas".

Australia Hourly Merchant Prices and Price Spikes

The Australia market is interesting as it has no capacity prices and allows price spikes. You can therefore use the markets in Australia to evaluate allowing price caps relative to attempting to make a separate capacity market (that requires some regulations) along with price caps on energy prices. There are two websites for the Australia market that are listed below. Both the websites are transparent and provide a lot of data. You can even get hourly prices along with the demand levels for five regions of Australia -- New South Wales, Southern Australia, Queensland and Victoria. It is painful to download the separate monthly files with history, but the good news is that once you have done this, you don't have to do it again. I have used a macro that allows you to put all files in a directory together in a single file. Then you can use the INDIRECT function to get all of the prices into a comprehensive database. The different files below demonstrate how to put the data together and evaluate the price spikes.

The initial files below are the summary databases that can be supplemented on a month by month basis. There are separate files for the different regions. The second set of files have the hour by hour techniques to put the data together from separate files.

Nordpool Price Trends and Price Patters to Study Hydro Markets and European Prices

The system price at in the Nordpool market can give insights into European markets and it demonstrates how a lot of hydro capacity can affect prices. Nordpool is a transparent website in which you can easily download hourly historic data. Currently, hourly data that begins in 2014 is available and I have put together monthly data that goes back to 1997. The file below shows the aggregated database with both the monthly history and the hourly detail. The data can be easily updated in an automated manner because of files on the Nordpool website that can be read into a workbook using the WORKBOOKS.OPEN statement in simple macro. Nordpool has correlation with natural gas prices but somewhat less than other markets because of the large amount of hydro power. The Nordpool market also has similar general trends as the German market discussed above. As price data is translated into USD from Euro and is translated from Norwegian to Euro, the file that I use to read in currencies is also presented.

Korea Price Trends

Although all of the generating plants in Korea are owned by KEPCO subsidiaries, there has been a market with bidding for many years. The website for the South Korean market looks like it reports hourly prices and I have some old hourly prices, but I had a bit of trouble retrieving the old prices. At any rate, the monthly prices are instructive in evaluating the prices in the comparative USD/MWH. Studying the market also demonstrates that as with so many other markets in the world, the natural gas price is highly correlated with electricity prices.


Electricity Supply Curve Analysis

Capacity Price Analysis

U.S. Electricity and Gas Prices
You can create a macro that reads from the internet and then puts outputs in different sheets. I find this one of the most useful things to do with excel.


Excel File


Video Link

Chapter Reference

Page Reference

Uploading German Electricity and Gas Data

Reads Data from Internet

Working with UK Electricity Price and Gas Data

Working with PJM Data

PJM Hub 1

Data Analysis


Automatically Uploading Natural Gas Prices

Natural Gas Read

Shows how to automatically Read

Data Analysis on Natural Gas Spreads

Natural Gas Spreads

Reading from Internet and Data


EIA Natural Gas Spreads

U.S. Spreads 1

Dense Data Analysis


PJM Merchant Plant Analysis

PJM Merchant Analysis

Long Video (a little boring)


U.S. Markets Data Analysis

EIA U.S. Data

Long Video (data analysis)


U.S. Markets Merchant Analysis by Region

EIA Merchant Analysis

Graphing and Analysis


PJM Supply Curve Analysis

PJM Supply Curve

Flexible Range Names with Offset

NEPOOL Supply Curve Analysis

NEPOOL Supply Curve

Supply and Demand Analysis

Simple Supply and Demand

Supply and Demand Analysis with Demand Curve

Supply and Demand with Elasticity

Simple Screening Analysis


Long-term Marginal Cost with Multiple Technologies
Long-term Pricing

Capacity Price Value

Capacity Price Analysis

Working with EIA 923 Data Base