Tutorial Part 2

Writing a macro


In the first section of the tutorial, we developed a spreadsheet that looked like this:


If you remember, we imported the table names in the top row, followed by the variables in the next row, then the descriptions, and finally, the actual data (the chart has been removed for simplicity). Now let’s make this a macro. First thing we need to do is Press Alt-F11 (VB Macro editor, also available by clicking tools, Macro, Visual Basic Editor). After the visual basic editor opens, click Insert, Module. The new module is where we will write our macro. You should now have a screen like:


We need to tell Excel that we will be referencing PDQData.com. Do this by clicking Tools, References, and checking the PDQData2 box. Then click ok. Now we are ready to start the macro. All macros start with “Sub” followed by a name. Type in “Sub Update” the press return (without the quotes). You’re module should look like:


When we were importing data from the steps in Tutorial – part 1, we:

  1. Opened a PDQData.com table,

  2. Selected the variables,

  3. Set a date range, and

  4. Imported the data


We will follow the same steps here, only with macro commands. The command to open a PDQData.com table where the table name is in a range cell is is PDQ_VB_ROPEN. On the line after the “Sub” line type PDQ_VB_ROPEN( and you will see that excel prompts you for the parameter with:

We type in the cell address enclosed in quotes followed by a closing parenthesis. The cell address can be typed as “C1” or “R1C3” (the latter refers to Row 1, Column 3).

The next line selects the PDQData.com variables. This command also takes a cell address, but since the variable are in a range of cells, we indicate it is a range by separating the beginning and ending cell addresses with a “:” (colon). The command to select the variables in cells “R2C3:R2C4” ( or C2:D2) is PDQ_VB_GET_VARIABLE("R2C3:R2C4"). Type this command on the next line.


Next we set the date range from cells R3C1:R4C1 (or A3:A4). The command to do this is PDQ_VB_GET_DATES("R3C1:R4C1"). Type this command on the next line.


Before we import the data, let’s erase any existing data. If we want to clear the range R4C3:R100C4 (I just picked down to row 100) or , the command using R1C1 notation is:

Range(cells(4,3),cells(100,4)).clear

Using the “A1” notation, the same command is:

Range(“C4:D100”).clear

Enter one of these formats on the next line.


Now to actually import the data. The command to import the data is PDQ_VB_IMPORT_DATA(“R4C3”). This will import the data beginning with Row 4, Column 3.

Now our macro should look like:


Go back to the spreadsheet and let’s test it. Press ALT-F8 (or Click Tools, Macro, Macros), and select Update from the list. The click the Run button. If you want to step through the macro and watch each step, Click the step button. Press the F8 key to execute the current step and move through each of the statements we typed in.


Next, we will import the futures prices. Again we go through the 4 steps for importing data. First we start with opening the table. The table name for the futures is LHNBYEXP. We’ll open the table by passing the name of the table to PDQData.com (we passed in the cell address above). The command to open the table is PDQ_VB_OPEN(“LHNBYEXP”)

Type this command on a line below the PDQ_VB_Import_Data line.


Next we select the variable like before with: PDQ_VB_GET_VARIABLE ("R2C5")


If you remember, we matched each date from column 3. The command to match a series of dates is PDQ_VB_MATCH_DATES ("R4C3:R100C3"). Notice how this points past the series of dates. This is OK. If a blank cell is passed in as a date to match, PDQData.com will return a blank row in that position.


Now to clear column 4 (where we will import the data, then import the data. These commands are:

Range(Cells(4, 5), Cells(100, 5)).Clear

PDQ_VB_Import_Data ("R4C5")


Our macro should look like:


Now to test it. Go back to the spreadsheet, press Alt-F8 then click update, and run.

To double check that the data is changing, let’s change the range in column 1, rows 3 and 4 to Jan 1, 2005 to April 30, 2005. Enter the date range by typing 1/1/5 in row 3 and 4/30/5 in row 4 then press enter. Now click Alt-F8, update, and run and the data should change.


Couple of notes. Sometimes you will receive an “Out of Memory” pop-up window. This is usually from importing too many data points. PDQData.com will only import 250,000 points in one query, and depending on the query, may import less. This limit is by design. The second message you may experience is a “bad formed XML documents”. Typically this is due to some error either in requesting the query, or receiving the response. Generally, if you wait a minute, and retry, the error message will go away.