Establishing a design for my excel project

[Chris435435]

Hi, I was on this forum over the summer (not too frequently) working as an intern at Brookhaven National Laboratory. My mentor assigned me an excel project. Basically, my summer consisted of collecting energy data (oil consumption figures, natural gas consumption figures, etc.) to be used as input into an energy forecasting model called MARKAL. Collecting this data every year is a painful procedure and so my mentor wanted me to construct excel sheets such that these spreadsheets are updated everytime new energy data is available (annual data).

I have returned in the fall to continue this project and I'd like to know how to approach this problem with the best methods. He suggested that I just link the data (which are available as excel spreadsheets) to the spreadsheets created specifically for MARKAL data. The problem here is that I'm assuming that all the data I'm drawing from the original files will be in the exact same place as the data that will come in next year.

For example, the data that I have used in constructing these spreadsheets comes from the Energy Information Administration website (www.eia.doe.gov). One of the files that I used is called sup_tran which has just been released in 2006. Basically, I linked whatever data I needed from this file to another spreadsheet. Now when sup_tran is released again in 2007, I can't be sure that everything will be in the same place... I'm assuming it will because that has been the trend every year.

I'd just like to make these spreadsheets that I'm putting together a little more reliable. Can you help? Do you need more information? Please let me know. Thanks.

 

[Todd Bardoni]

These things require a little "playing around". You can implement web queries into your spreadsheet, create dynamic ranges, etc, etc. But if the data should move in the other spreadsheets or on the website (if performing a web query) then there's not much you can do about that. You will simply have to set up your spreadsheet in a way that can be easily edited. If the data has been consistent in format then I think that says a lot about the potential maintenance level on your end. Just dive in and make the necessary amendments along the way.

Hope this was somewhat helpful.