Export excel and format excel via macro in Stata

[Mathias Enichlmayr]

Hi there,

I export 20 excel files in Stata every week and have to format all of the excel files the same way (e.g. highlight header, use bold font, set filters, ...).
I can, obviously, record all those formatting steps via the "record a macro" function in Excel. Now my question:
Is it possible to do the formatting in Stata (e.g. import the recorded macro in stata) so the exported excel file is already perfectly formatted?
Help is very much appreciated.

Regards
Mathias

[Chris Vecchio]

Which version of Stata are you using? If you have Stata 13 or greater, you can use the putexcel command to format spreadsheets. You can also try Mata's xl()class for some additional capabilities.

I also create sets of spreadsheets on a regular basis. These spreadsheets use Excel charts and links from one worksheet to another. For these more advanced Excel operations, I use Python in conjunction with the pywin32 package to run vba created from the macro recorder. Converting the vba to equivalent Python code can involve quite a bit of trial and error. I can provide some sample code if you'd like. My basic process for creating the spreadsheets is as follows:

1. Import and process data using Stata.
2. Export data to Excel and do most basic formatting tasks using putexcel or xl().
3. Define local macros with formatting options and other info that will be passed via the command line to Python.
4. Call Python program that modifies the Excel spreadsheet.

[Mathias Enichlmayr]

Hi Chris,
first of all thanks for your reply.
I am using Stata 13. Unfortunately, the formatting options using putexcel or xl() do not offer enough possibilities. The only possibility I see is to record a macro and implement this Macro in Stata. Since this might not be possible you solve this problem using Phython. Please correct me if I misunderstood you:
Since you have Phython you record the Macro and insert the recorded macro code in Phython (doing some adjustments along the way). Afterwards, you execute the code in Phython and the program formats all your excel files. Is this correct? (I have never heard of Phython but I might be able to get it anyway).
Cheers
Mathias

[Chris Vecchio]

Hi Chris,
Since you have Phython you record the Macro and insert the recorded macro code in Phython (doing some adjustments along the way). Afterwards, you execute the code in Phython and the program formats all your excel files. Is this correct?

That's correct. I use Stata to process the data and export it to Excel. I then use putexcel to format cells and the worksheet. I then call a Python program from within my do-file that adds charts and makes other changes that Stata can't make.

I have never heard of Phython but I might be able to get it anyway.

Python is a programming language. You can get more information about it here. I use the Anaconda distribution of Python found here.

If this method interests you, there are several resources you can look at such as this tutorial and this site. The author of the pywin32 package has a nice intro site here. You can google "python win32com excel" for other relevant results.

Here is a basic example of my process. I use Stata to export some time series data to Excel and call the Python program to do the equivalent vba operations from the macro recorder. I should mention I am by no means a proficient Python programmer. I use Python for tasks I can't get Stata to do.

Do-file

sysuse tsline2, clear

export excel using "C:\Users\user\desktop\temp\testfile.xlsx", firstrow(variables) replace

! python "C:\Users\user\desktop\temp\createchart.py"

VBA output from macro recorder

Sub CreateChart()
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Range("A306:C366")
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.ChartArea.Border.LineStyle = msoFalse
    ActiveChart.Axes(xlCategory).MajorUnit = 14
    ActiveChart.Axes(xlCategory).AxisBetweenCategories = False
    Selection.TickLabels.NumberFormat = "m/d/yy;@"
    Selection.MajorTickMark = xlInside
    ActiveChart.Axes(xlValue).Select
    Selection.MajorTickMark = xlInside
    ActiveChart.Axes(xlValue).MinimumScale = 2500
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(158, 0, 0)
        .Transparency = 0
        .Weight = 3.25
    End With
End Sub

Python program (createchart.py) with converted vba code

import pythoncom
import win32com.client as win32
from win32com.client import constants as c
import os
import wmi
import time
cw = wmi.WMI ()
pythoncom.CoInitialize()

file = "C:\\Users\\user\\Desktop\\temp\\testfile.xlsx"

# Create new instance of Excel
xlApp = win32.gencache.EnsureDispatch('Excel.Application')

# Force the application window to be visible
xlApp.Visible = True

# Disable Excel alerts such as save on closing message
xlApp.DisplayAlerts = False

# Open the workbook using the Excel instance
xlBook = xlApp.Workbooks.Open(file)

# Define sh as the particular source data worksheet
sh = xlBook.Sheets("Sheet1")

# Make the worksheet active so we can modify it
xlApp.Sheets(sh.Name).Activate()

# Add the chart
shChart = xlApp.ActiveSheet.Shapes.AddChart(c.xlLine,Left=210,Top=0,Width=375,Height=200).Select()

# Set range of chart source data to remove column D and show only last 2 months
xlApp.ActiveChart.SetSourceData(Source=sh.Range("$A$306:$C$366"))

# Remove thin border from chart
xlApp.ActiveChart.ChartArea.Border.LineStyle = c.xlNone

# Make the x-axis labels appear only every 14 days
xlApp.ActiveChart.Axes(c.xlCategory).MajorUnit = 14

# Make tick marks and labels appear on categories rather than between
xlApp.ActiveChart.Axes(c.xlCategory).AxisBetweenCategories = False

# Format the x-axis labels to m/d/yy
xlApp.ActiveChart.Axes(c.xlCategory).TickLabels.NumberFormat = "m/d/yy;@"
 
# Make X-Axis tick marks inside
xlApp.ActiveChart.Axes(c.xlCategory).MajorTickMark = c.xlTickMarkInside

# Make Y-Axis tick marks inside
xlApp.ActiveChart.Axes(c.xlValue,c.xlPrimary).MajorTickMark = c.xlTickMarkInside

# Change minimum axis value for Y-axis
xlApp.ActiveChart.Axes(c.xlValue).MinimumScale = 2500

# Change second series to red color and change line weight
xlApp.ActiveChart.SeriesCollection(2).Border.Color = 158
xlApp.ActiveChart.SeriesCollection(2).Format.Line.Weight = 3.25

# Pause for 5 seconds to see result
time.sleep(5)

# Save changes, close workbook, and make sure Excel process is completely closed
xlApp.ActiveWorkbook.Save()
xlApp.ActiveWorkbook.Close(SaveChanges=0)
xlApp.Quit()

# Remove Excel from task manager if it is still there
del xlApp
pythoncom.CoUninitialize()

[Bjarte Aagnes]

You might have a look at VBScript. Scripts may be generated by Stata's file command and started via Stata's shell or winexec commands.

[Belinda Foster]

Hello. Interesting approach Chris Vecchio. But the solution proposed (including the VBscript alternative) is platform specific i.e. Windows. Does anyone have any ideas how this process can become platform independent i.e. support Macs?

[Chris Vecchio]

Hi Belinda Foster. Sorry for the late reply. There are several Python packages that can write Excel files on multiple platforms. See http://www.python-excel.org/ for a list. I went with the pywin32 solution after finding each of these packages to be lacking key features I needed. These issues ranged from no support for a chart type I needed to only being able to write to an empty Excel file. I haven't checked the status of these packages in a while, but openpyxl looks to have a bunch of new features.