If you visit the Web site and pull up a quotation for any stock, you will find a Uniform Resource Locator (URL) link under the basic quote data labeled “Download Data,” which lets users download a CSV file suitable for importing into Microsoft Excel or Intuit Quicken:

If your browser’s MIME settings are set correctly, your browser will actually launch Excel with the resulting data. This is due primarily to the final variable (key-value) pair found in the link, e=.csv. This variable is actually not used by the server as it always sends back data in CSV format anyway.

If we use our friend urllib.urlopen(), we see that for any stock ticker symbol, one CSV string is returned:

The string would then have to be manually parsed (by stripping the trailing whitespace and splitting on the comma delimiter). As an alternative to parsing the data string ourselves, we can use the csv module, introduced in Python 2.3, which does both the string split and the whitespace strip. Using csv, we can replace the for loop above with the following assuming all other lines are left intact:

By analyzing the argument field f passed to the server and from reading Yahoo!’s online help for this service, you will see that the symbols (sl1d1t1c1ohgv) correspond to: ticker symbol, last price, date, time, change, open price, daily high, daily low, and volume.

You can get more information by checking the Yahoo! Finance Help pages—just search for “download data” or “download spreadsheet format.” Further analysis of the API reveals a few more options such as the previous closing price, the percentage change of the current price to the previous close, the 52-week high and low, etc. All in all, the options can be summarized in Table 23.1 along with the formats of the returned components.

The field names are given in the order you want them from the server. Just concatenate them together as a single argument to the field parameter f as part of the requesting URL. As mentioned in the returned value footnote, some of components returned are quoted separately. It is up to the parser to properly extract the data. Observe the resulting (sub)strings when parsed manually vs. using the csv module in our example above. If a value is not available, the quote server returns “N/A” (separately quoted since that field is, which makes it consistent ... a good thing).

For example, if we give the server a field request of f=sl1d1c1p2, we get a string like the following returned for a valid stock ticker:

For the case where the stock is no longer publicly traded, we get something like this instead (note again how fields that come back quoted still do, even if N/A):

The quote server will also allow you to specify multiple stock ticker symbols, as in s=YHOO,GOOG,EBAY,AMZN. You will get back one row of data like the above for each company. Just keep in mind that “[any] redistribution of quotes data displayed on Yahoo! is strictly prohibited,” as quoted in the Yahoo! Finance Help pages, so you should only be using these data for personal reasons. Also be aware that all of the quotes you download are delayed.

Using what we know now, let us build an example (Example 23.1) application that will read and display some stock quote data for some of our favorite Internet companies.

If we run this script, we will get output that looks like the following:

We can use Component Object Model, better known as COM (or its marketing name, ActiveX), to communicate with tools such as Outlook and Excel. For programmers, the pleasure comes with being able to “control” a native Office application directly from their Python code.

Specifically, when discussing the use of a COM object, e.g., launching of an application and allowing code to access methods and data of that applications, this is referred to as COM client-side programming. Server-side COM programming is where you are implementing a COM object for clients to access.

Core Note: Python and Microsoft COM (Client-Side) Programming

Python on the Windows 32-bit platform contains connectivity to COM, a Microsoft interfacing technology that allows objects to talk to one another, or more higher-level applications to talk to one another, without any language- or format-dependence. We will see in this section how the combination of Python and COM (client programming) presents a unique opportunity to create scripts that can communicate directly with Microsoft Office applications such as Word, Excel, PowerPoint, and Outlook.

The prerequisites to this section include running on a Win32 platform with both Python and the Windows Extensions for Python installed. You must also have one or more Microsoft applications available to try the examples with. The download instructions for the Windows Extensions should be adequate to get your system ready to go. Since PythonWin comes with the Extensions distribution, we recommend IDE for building and testing your Win32 scripts.

In this section, we will show you how you can interact with an Office application. We will present a few examples, some of them quite useful, and describe how they work in detail. You will also find several of these at the “Python Cookbook” Web site. We confess to readers that we are not COM or VisualBasic experts and we are well aware that our examples can be vastly improved. We would like to solicit the readership to drop us a line and send us any comments, suggestions, or improvements that you would consider for the general audience.

Let us start with very simple examples of how to launch and interact with Microsoft Excel, Word, PowerPoint, and Outlook. Before we show you examples of all we have discussed, we want to point out that client-side COM applications all follow similar steps in execution. The typical way in which you would interact with these applications is something like this:

1. Launch application
2. Add appropriate document to work on (or load one from disk)
3. Make application visible (if desired)
4. Perform all desired work on document
5. Save or discard document
6. Quit

Enough talking ... let us take a look at some code. Below are a series of scripts that control a different Microsoft application. All import the win32com. client module as well as a couple of Tk modules to control the launching (and completion) of each application. Also like we did in Chapter 19, we use the .pyw file extension to suppress the unneeded DOS command window.

Our first example is a demonstration using Excel. Of the entire Office suite, we find Excel to be the most programmable. It is quite useful to pass data to Excel so that you can both take advantage of the spreadsheet’s features as well as viewing data in a nice printable format. It is also useful to be able to read data from a spreadsheet and process data with the power of a real programming language like Python. We will present a more complex example using Excel at the end of this section, but we have to start somewhere. So, we start with Example 23.2.

The next demonstration is with Word. Using Word for documents is not as applicable to the programming world as there is not much data involved. One could consider using Word for generating form letters, however. In Example 23.3, we create a document by simply writing one line of text after another.

The Word example follows pretty much the same script as the Excel example. The only difference is that instead of writing in cells, we have to insert the strings into the text “range” of our document and move the cursor forward after each write. We also have to manually provide the line termination characters of carriage RETURN following by NEWLINE ( ).

If we run this script, a resulting screen might look like Figure 23-2.

Figure 23-2. Python-to-Word demonstration script (word.pyw)

Applying PowerPoint in an application may not seem commonplace, but you could consider using it when you are rushed to make a presentation. You can create your bullet points in a text file on the plane, then upon arrival at the hotel that evening, use a script that parses the file and auto-generates a set of slides. You can further enhance those slides by adding in a background, animation, etc., all of which are possible through the COM interface. Another use case would be if you had to auto-generate or modify new or existing presentations. You can create a COM script controlled via a shell script to create and tweak each presentation generated. Okay, enough speculation ... now let us take a look at our PowerPoint example (i.e., Example 23.4).

Again, you will notice similarities to both the Excel and Word demonstrations above. Where PowerPoint differs is in the objects you write data to. Instead of a single active sheet or document, PowerPoint is somewhat trickier because each presentation slide can have a different layout. With a presentation, you have multiple slides, and each slide can have a different layout. (Recent versions of PowerPoint have 30 different layouts!) The actions you can perform on a slide depend on which layout you have chosen for each page.

In our example, we just use a (title and) text layout (line 17) and fill in the main title (lines 19–20), Shape[0] or Shape(1)—Python sequences begin at index 0 while Microsoft software starts at 1—, and the text portion (lines 22–26), Shape[1] or Shape(2). To figure out which constant to use, you will need a list of all the ones available to you. For example, ppLayoutText is defined as a constant with a value of 2 (integer), ppLayoutTitle is 1, etc. You can find the constants in most Microsoft VB/Office programming books or online by just searching on the names. Alternatively, you can just use the integer constants without having to name them via win32.constants.

The PowerPoint screenshot is shown in Figure 23-3.

Figure 23-3. Python-to-PowerPoint demonstration script (ppoint.pyw)

Finally, we give an Outlook demonstration, which uses even more constants than PowerPoint. As a fairly common and versatile tool, use of Outlook in an application makes sense, like it does for Excel. There are always e-mail addresses, messages, and other data that can be easily manipulated in a Python program. Example 23.5 is an Outlook example that does a little bit more than our previous examples.

In this example, we use Outlook to send an e-mail to ourselves. In order to make the demonstration work, you need to turn off your network access so that you do not really send the message, and thus are able to view it in your Outbox folder (and delete it if desired after viewing it). After launching Outlook, we create a new mail message and fill out the various fields such as recipient, subject, and body (lines 15–21). We then call the send() method (line 22) to spool the message to the Outbox where it will be moved to “Sent Mail” once the message has actually been transmitted to the mail server.

Like PowerPoint, there are many constants available ... olMailItem (with a constant value of 0) is the one used for e-mail messages. Other popular Outlook items include olAppointmentItem (1), olContactItem (2), and olTaskItem (3). Of course, there are more, so you will have to find a VB/Office programming book or search for the constants and their values online.

In the next section (lines 24–27), we use another constant, olFolderOutbox (4), to open the Outbox folder and bring it up for display. We find the most recent item (hopefully the one we just created) and display it as well. Other popular folders include: olFolderInbox (6), olFolderCalendar (9), olFolderContacts (10), olFolderDrafts (16), olFolderSentMail (5), and olFolderTasks (13). If you use dynamic dispatch, you will likely have to use the numeric values instead of the constants’ names (see previous Core Note).

Figure 23-4 shows a screen capture of just the message window.

Figure 23-4. Python-to-Outlook demonstration script (olook.pyw)

Before we get this far, however, from its history we know that Outlook has been vulnerable to all kinds of attacks, so Microsoft has built some protection into Outlook for restricting access to your address book and being able to send mail on your behalf. When attempting to access your Outlook data, the screen shown in Figure 23-5 pops up where you must explicitly give permission to an outside program.

Figure 23-5. Outlook address book access warning

Then when you are trying to send a message from an external program, you get the warning dialog shown in Figure 23-6, where you have to wait until the timer expires before you are allowed to select “Yes”.

Figure 23-6. Outlook e-mail transmission warning

Once you pass all the security checks, everything else should work smoothly. There is software available to help get you around these checks but they have to be downloaded and installed separately.

On this book’s Web site at http://corepython.com, you will find an alternative script that combines these four smaller ones into a single application that lets users choose which of these demonstrations to run.

Now that we have gotten a good taste of Office programming, let us build a more useful application by combining the material from this section with that of the Web Services section. If we were to combine the stock quote example with our Excel demonstration script, we would get an application that would download stock quotes from the net and pop them directly into Excel without having to create or use CSV files as a medium (see Example 23.6).

Jython is one of those tools that can unite two diverse programming populations. For one, it caters to Python programmers embedded in a Java development environment and gives them the ability to rapidly prototype solutions that seamlessly integrate into an existing Java platform. Another reason is that it helps simplify the lives of millions of Java programmers out there by giving Java a scripting language environment. No longer do Java programmers have to write a test harness or driver application to simply test a class they have just written.

Jython gives you most of what Python has to offer along with the ability to instantiate and interact with Java classes too! Jython code is dynamically compiled into Java bytecode, plus you can extend Java classes in Jython. You can also extend Python using Java. It is quite easy to write a class in Python and then use it as a Java class. You can always statically compile a Jython script into Java bytecode.

Jython can be downloaded from the book’s Web site or at http://jython.org. After installation and seeing some default startup notices of processing new .jar files, starting up Jython’s interactive interpreter looks eerily like you’re using Python. And yes, Virginia, you can still do the same old “Hello World!” in Python:

The only difference is that you now have (to wait for) Java’s long startup time. Once you have accepted that inevitability, you can move on to greater things. The more interesting thing about the Jython interactive interpreter is that now you can do “Hello World!” using Java(!):

Java gives Python users the added bonuses of native exception handling (not available in standard Python, or “CPython” as it is called, when being referred to among other implementations) as well as use of Java’s own garbage collector (so Python’s did not have to be [re]implemented for Java).

By having access to all Java classes, we have a much broader universe of what is possible. One example is GUI development. In Python, we have the default GUI of Tk via the Tkinter module, but Tk is not a native Python toolkit. However, Java does have Swing, and it is native. With Jython, we can actually write a GUI application using Swing components ... not with Java, but using Python.

A simple “Hello World!” GUI written in Java followed by its equivalent in Python is given in Examples 23.7 and 23.8, respectively, both of which mimic the Tk examples tkhello3.py found earlier in the GUI programming chapter. These programs are called swhello.java and swhello.py, respectively.

The code for both matches that of tkhello3.py except they use Swing instead of Tk. The hallmark of the Python version is the significant reduction in the number of lines of code necessary to do the same thing in Java. The Python code is more expressive, with each line of code having more significance. In short, there is less “white noise.” Java code tends to have a lot more boilerplate code to get work done, while Python lets you concentrate on the important parts of your application: the solution to the problem you are trying to solve.

Since both applications are compiled to Java bytecode, it is no surprise that both applications look exactly alike when executing on the same platform (see Figure 23-8).

Figure 23-8. Swing Hello World Demonstration Scripts (swhello.{java,py})

Jython is a great development tool because you get the expressiveness of Python plus the rich API in the Java libraries. If you are a current Java developer, we hope that we have whet your appetite in terms of what you can now do with the power of Python behind you. If you are new to Java, Jython will be able to ease you in gently. You can prototype in Jython, then port easily to Java as necessary.