Economics should be open

December 16, 2008

Data cleaning, excel

Filed under: coding, Excel, Open Source, Uncategorized — Tags: , , , — howardchong @ 10:17 pm

Ever have data with commas between the thousand and million marks? spaces at the end of numbers? text footnotes appended to numbers, spaces in numbers?

Cleaning this by hand is a complete pain, so I wrote an Excel Macro.

To install, record a new macro and then stick this in the module.

There’s even a “careful” mode. (Change the line of careful=false to careful=true) which prompts you for each change that I felt might be wrong.

Please write a comment if you find it helpful.




December 9, 2008

Polish Heat and Power

Filed under: Energy — Tags: , — howardchong @ 1:01 am

I was looking at International Energy Agency (IEA) stats on Electricity Generation. For most countries, an average of 90% of electricity is produced by electricity-only plants. But in Poland, 91% is produced by combined heat and power (CHP).

I thought this must be a mistake, but this excerpt from a googled site shows otherwise:

“Heat and hot water
The district heating sector in Poland comprises almost 400 individual networks, including the world’s largest district heating network in Warsaw. Of Poland’s urban population, 70% receive space-heat and 50% receive hot water from district heating systems. The sector is powered by a total of over 8,000 boilers, and delivers about 488 PJ (petajoules) of heat each year, at a peak rate of 45 GW. Out of this total heat production, approximately 170 PJ is produced within, and used by industrial enterprises.”
(source:, accessed 08 DEC, 2008 )

Wow! What does this mean? It means that instead of every house having it’s own water heater, there is a big chunk of Poland that gets it’s hot water from a central plant. Instead of everyone having their own heater, some people also get heat piped in from a central heating plant.

December 2, 2008

MS Access Tables to Stata, for Residential Energy Consumption Survey

Problem: To convert MS Access tables (of the EIA Residential Energy Consumption Survey 1997 data) to CSV files for STATA import

This is also a general script for converting MS Tables to CSVs

Solution: Wrote a VBA script


  1. Open MS Access
  2. Tools | Macros | Visual Basic Editor
  3. Create a new module on your database by doing: Right Clickon database | Insert | Module
  4. Copy the following text (without the line numbers)
    1. Sub ExportAllTablesCsv()
    2. Dim dbMyDB As Database
    3. Set dbMyDB = OpenDatabase(“recs97_converted.mdb”)
    4.     For Each tdfCurrent In dbMyDB.TableDefs
    5.         fileoutname = “C:\” & tdfCurrent.Name & “.csv”
    6.         If Left(tdfCurrent.Name, 2) <> “MS” Then
    7.             DoCmd.TransferText acExportDelim, , tdfCurrent.Name, fileoutname, TRUE
    8.         End If
    10.     Next
    11. End Sub
  5. For your customization:
    1. change your mdb file to your mdb file
    2. The If statement (line 10) is there to deal with the fact that there are certain system tables that I needed to skip. None of my tables started with “MS”, so this was a simple non-general fix.
    3. If you look up help for TransferText it has a SpecificationName. I left that argument blank. You can change to tabs or other things using that.
    4. I had trouble with getting column headings, but jwhite at showed me the light. This code now gives column headings with the TRUE argument.  For more details, check out

If you can think of a better solution, I’d love your thoughts!

Other possible/failed solutions:

  • I tried running a query that joined all the tables with the EIA ID Num as the key for each table. Though this was easy, I got the “too many fields” error.
  • I think there is a way to use SQL (or something???) to select all the tables and write them and to import those into another program. STATA, to my limited knowledge, doesn’t do SQL. 
  • STATA supposedly does XML. One can use TransferText to write XML too. I don’t know XML well enough to try it.

Any comment appreciated.





Create a free website or blog at