Tutorial
Let’s assume you want to compute the spread between the Loin price reported by the USDA (daily) and the futures price for lean hogs over the January to April period in 2006.
The spreadsheet is available for download by clicking here
For the purpose of this example, we’re going to look at “LOIN BONE-IN 1/4" TRIM 21#DN”. Now, this price is reported by AMS, an agency of the USDA. So first click PDQData.com on the excel menu, and you’ll see:
The first thing we do is Select a Table. Click that option. Next you’ll see
We will double click the data categories in the left box, until we see the desired data table in the box on the right. So, in the left box double click:
USDA
AMS
Livestock and Meat
Meat and Indices
Pork
Carlot Pork
Average Prices
Daily
You’ll see:
Click on the “Central US Carlot pork report, Wghtd Avg Prices in the box on the right.
You’ll return to the spreadsheet for the next step.
In the next step, we tell PDQData the actual data item we want from this report (or Table). Click PDQData.com, then Variables. You’ll see the following sceen:
The box on the left has all of the data items in this table, and the box on the right is what you have selected. Click the word Date in the box on the left, and it will show in the box on the right. Follow that by clicking on “LOIN BONE-IN ¼” TRIM 21#DN-LGT Total FOB Basis Avg” and it will show on the box on the right. Then click OK.
The third step is to tell PDQData the date range we are interested in. In a blank cell in the spreadsheet, enter the date “01/01/06” for January 01, 2006 and press enter. In the cell below that cell, enter our ending date by typing 4/30/6 for April 30, 2006. Your spreadsheet should look something like:
Next click on PDQData.com and Set Date Range. A message box will pop up asking you to enter the cells that contain the date range. Highlight the two cells we entered the beginning and ending dates, then click OK.
Finally, after 1) selecting a table, 2) Selecting the variables, and 3) setting a date range, we are ready to bring in the data. Click PDQData.com then import data. You’ll be prompted for the top left cell of the data range. Click on a blank cell (you may want to leave a row or two above this range, so we can import the descriptions later) then click OK.
Voila, there is the data, just as if you typed each entry in the spreadsheet.
Now so we can remember what data we selected, click PDQData.com and Import Variable Descriptions. When the box appears asking for the upper left corner of the import range, highlight the cell right above the first data date (i.e. the cell right above the 1/4/2006, then click OK
You should now have a spreadsheet something like:
You’ll notice that there are several blank cells in the data series. The USDA does not report data unless there are sufficient numbers of trades for the day. PDQData databases the data reported by the USDA. If a specific series is not reported for a specific day, a blank cell will be in the series.
Before we open the table with the futures prices, let’s save the table name and variables we selected so we will be able to write a macro to automate this process later. If necessary, insert two rows above the imported variable descriptions. Then click PDQData.com and import table. Import the table name in a blank cell.
Next import the variable name by clicking PDQData.com, then click Import Variables. Import the variable names in the row right above the descriptions.
Your spreadsheet should now look something like:
Now, to import the futures prices corresponding to the dates imported earlier. Click PDQData.com, the click Select Table. You’ll see a category “Futures Markets”. Double click this category. The category list then displays a list of exchanges. Double click CME, then click Lean hogs. The box on the right will list the table “Lean Hogs-CMENearby roll on exp.”. PDQData computes two nearby series of futures, one that rolls on the first of the month, and one that rolls when the contract expires. In this case, we want the one that rolls on expiration.
Next we select the variable. Click PDQData.com, then click Variables. You’ll see:
In this case, just click the settle. Then click OK.
Now to set the date range. We could skip this step since we set the date range earlier. But to ensure that the futures quotes match the dates we imported for the loins, we’ll use the match dates function of PDQData.com. Click PDQData.com, then click Set Date Match Range. A dialog box will appear asking for the range of dates to match to. Highlight the complete range of dates we imported earlier, then click OK.
Now to import data. Click PDQData.com, then click import data. When the dialog box appears asking for the upper right corner of the range to hold the data, click the cell just to the right of the price on 1/4/2006 (the 95.91), then click OK
Again, we can import the variable description above our data series.
The spreadsheet should now look something like:
You can see the importance of matching dates. January 16, 2006 was a holiday for the exchange (i.e. there was no commodity quote that day), but there was a quote from the USDA for pork prices.
Finally, import the table name above the futures prices column, then import the variables in the next row. Your spreadsheet should look like:
We can now enter an equation to compute the spread. Since I don’t want to include days that have no quote, I entered the equation:
=if(and(RC[-1]>0,RC[-2]>0),RC[-2]-RC[-1],na())
Basically, this says if both data points are greater than zero, compute the spread, else put in a NA.
After copying the formula down, and generating a chart, you’ll have something like:
Our next step is to automate the steps to make this a macro driven spreadsheet. Save this sheet, then continue with part 2 of the tutorial
Writing a macro by clicking here