*** Edit 22 May 2020 *** Updated with API capabilities: https://vinzjeannin.wordpress.com/2020/05/22/using-the-usda-usda_ers-cornell-api-in-excel-vba-to-retrieve-wasde-data-for-the-heatmap-wheat-corn-soybeans-oatt-visual-basic-visualbasic-macro-automation-json-xml/ *** Edit 09 May 2020 *** Please see following post for update: https://vinzjeannin.wordpress.com/2020/05/09/update-of-the-heatmap-cheat-sheet-usda-wasde-report-excel-vba-macro-automation-wheat-corn-soybeans-oatt/ *** Edit 23 January 2018 *** The Excel file was update, it should now work for 2018. There is also another macro on sheets “Param”. A button Export is exporting the Heatmaps as .JPG file, then it can be used on social media, presentations, etc,… In the cell L5, the path needs to be inputted, it should end by the backslash (), well should do a quick error handling of this,… On the next release maybe!  Also, there is now a Twitter sheet, in order to send Tweets about the USDA WASDE automatically. API keys need to be entered in column N appropriately, please refer to the following post for assistance: Send Status Update (Tweets) to Twitter using Excel and VBA, XML requesting the Twitter API with HMAC-SHA1 encryption  The cell B1 is used for the sensitivity. Sending Tweets with every single move will be like flooding. It’s in million tons, every movement equal or above (in absolute value) will generate a Tweet. Because you may want to assess the number of Tweets before sending them, the button “Gather Tweets” will create the Tweets depending on the sensitivity and the count will be provided in B2. If you are happy, here you go, click on “Send Tweets” (as per previous paragraphs API keys need to be filled appropriately). For security purpose, “Gather Tweet” step is compulsory and needs to be completed 75 seconds before clicking on “Send Tweets”, this is to ensure you have actually generated the Tweets on the appropriate data set and that you are happy of the number of Tweets you are about to send. Indeed, there’s a limit on API request, especially for sending Tweets, and it’s easy to get blocked by Twitter, so be careful! More info, here: Rate limits. Twitter is trigger happy to block API abusers, so be careful, you are warned! Anyway, this is for information purpose only in theory, and if one geek is doing it (me), do we really need another one to flood Twitter with simialar Tweets? Probably not, but you are more than welcome to use the code to develop something else. Anyway, to avoid getting blocked (I got blocked 4 or 5 times already), I have created and optimization macro. In M2, the number of Tweets targeted is to be input, a click on “Optim” button will basically calculate a sensitivity tight enough to generate only the number of required Tweets and will post automatically. Next step of the project is to launch on a task scheduler the program and get the Tweets to be posted just after the WASDE report is published.  *** End Edit *** Most of the time, the first look we want to have a look at is how USDA is revising its report, more than the actual numbers as it’s the first thing that is coming into mind to asses the directional bias it could give to the market. Having the movements in one click as far as Wheat, Corn and Soybeans are concerned is pretty convenient indeed. USDA is publishing the WASDE report in 3 formats: Excel (.xls), PDF and XML. The last installment has a static URL, if we consider the Excel version, it is the following: http://www.usda.gov/oce/commodity/wasde/latest.xls That make easy and straight forward for the new crop from the month of June, second estimates of the new crop (so there’s an existing movement from May). Macro will simply make the difference between June and May, data are next to each other in the USDA’s Excel file. Code is pretty straightforward, looping lines and columns of the new crop balance sheet, there’s just a constraint, as the line “Selected Other” has been kept, it need to be jumped (this is the case for Corn and Wheat but this line doesn’t exist for Soybeans). Example for Wheat, code was created as an independent routine for each commodity:
Sub Wheat()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
WASDE = ThisWorkbook.Sheets("Param").Range("D4")
Workbooks.Open Filename:=WASDE
PthFile = ActiveWorkbook.Name
ThisWorkbook.Activate

ThisWorkbook.Sheets("Wheat New").Select


Sht = ThisWorkbook.Sheets("Param").Range("D7")


Line = ThisWorkbook.Sheets("Param").Range("E7")
DesLn = 5
shifCol = 0

For Col = 1 To 7

Do While Workbooks(PthFile).Sheets(Sht).Cells(Line, 2) <> ""
    CurVal = Workbooks(PthFile).Sheets(Sht).Cells(Line, Col + 2)
    PrevV = Workbooks(PthFile).Sheets(Sht).Cells(Line - 1, Col + 2)

    ThisWorkbook.Sheets("Wheat New").Cells(DesLn, Col + 3 + shifCol) = CurVal
    ThisWorkbook.Sheets("Wheat New").Cells(DesLn + 25, Col + 3 + shifCol) = Round(CurVal - PrevV, 2)

    Line = Line + 2
    DesLn = DesLn + 1

    If Line = 42 Then
        Line = 43
        DesLn = DesLn + 1
    End If
Loop
Line = 12
DesLn = 5
    If Col = 4 Then
        shifCol = 1
    End If
Next Col

Workbooks(PthFile).Close (False)

End Sub
For the old crop, there’s an issue. USDA is only giving the latest values, and in order to calculate the variations from the previous month to the current, the previous USDA report needs to be opened. But there’s no static URL and the URL contains the day of the publication, example for the May report, published on the 10th of May: http://usda.mannlib.cornell.edu/usda/waob/wasde//2010s/2016/wasde-05-10-2016.xls  So there’s a manual component, a table has to be created in Excel to show the dates of the publication, so far they are all input until the December report, in other words, this Excel cheat cheat will work only until the January 2017 report before needing an update. The URL is computed in the sheets “Param”. This shit also contains, by the way, the names of the sheets of the USDA WASDE report that we are looking for, the lines where the balance sheets are starting into the sheet. It will make easier to make modifications in case USDA is changing the format of the report. So the macro will open the 2 USDA reports, the latest and the month before, and instead, as previously, taking the data from the same report, it takes them from 2 different spreadsheet, so there’s no fundamental change. Example for Wheat, here is the section of the code, slightly better program than the previous as there’s only one routine for the 3 commodities:
Application.ScreenUpdating = False
Application.DisplayAlerts = False

WASDE = ThisWorkbook.Sheets("Param").Range("D4")
Workbooks.Open Filename:=WASDE
PthFile = ActiveWorkbook.Name

latest = Workbooks(PthFile).Sheets(2).Range("A1")

ThisWorkbook.Sheets("Param").Range("E4") = latest

PthPrev = ThisWorkbook.Sheets("Param").Range("D11")
Workbooks.Open Filename:=PthPrev
Previous = ActiveWorkbook.Name

ThisWorkbook.Activate

'''''''''''''''''''''''''' Wheat ''''''''''''''''''''''''''
ThisWorkbook.Sheets("Wheat Old").Select

LastSht = ThisWorkbook.Sheets("Param").Range("F7")
LastLine = ThisWorkbook.Sheets("Param").Range("G7") - 1

PrevSht = ThisWorkbook.Sheets("Param").Range("D14")
PrevLine = ThisWorkbook.Sheets("Param").Range("E14") - 1

DesLn = 5
shifCol = 0
Line = 1

For Col = 1 To 7

Do While Workbooks(PthFile).Sheets(LastSht).Cells(LastLine + Line, 2) <> ""
    CurVal = Workbooks(PthFile).Sheets(LastSht).Cells(LastLine + Line, Col + 1)
    PrevV = Workbooks(Previous).Sheets(PrevSht).Cells(PrevLine + Line, Col + 1)

    ThisWorkbook.Sheets("Wheat Old").Cells(DesLn, Col + 3 + shifCol) = CurVal
    ThisWorkbook.Sheets("Wheat Old").Cells(DesLn + 25, Col + 3 + shifCol) = Round(CurVal - PrevV, 2)

    Line = Line + 1
    DesLn = DesLn + 1

    If Line = 16 Then
        Line = 17
        DesLn = DesLn + 1
    End If
Loop
Line = 1
DesLn = 5
    If Col = 4 Then
        shifCol = 1
    End If
Next Col


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
That’s all! Each commodity has 3 sheets, let’s keep Wheat as an example:
  • “Wheat Old”: this is the old crop balance sheet, actual numbers and movements from the previous USDA WASDE report;
  • “Wheat New”: this is the new crop balance sheet, actual numbers and movements from the previous USDA WASDE report;
  • “Wheat Old to New”: this is the moments between the actual new crop numbers and the actual old crop numbers.
There’s also the first sheet called “SumUp” showing ending stocks change between the current USDA WASDE report and the previous one, as well as current stock to production ratios and stock to consumption ratios. All this should be enough to have a first quick look at the USDA WASDE report and asses the directional bias it could give to the market: Spreadsheet is downloadable here, don’t forget to enable the macros , it has been uploaded updated with the June 2017 values but on the first sheet (“SumUp”), just need to glick on the ‘Go !’ button to update the spreadsheet: Click Here To conclude:
  • It will need an update if USDA changes the format (unlikely to happen though);
  • It will need an update for it to work beyond the January 2017 report (it can be updated as soon as 2017 publications dates are known);
  • May (2017, 2018,…) reports will be a challenge as the new crop is rolled into old crop and the new new crop cannot be compared with previous values as it is the first estimation;
  • Macro can be updated as for the new crop, it’s opening and closing 3 times the latest USDA WASDE report and after reopening it, for the old crop computations, this is far from being optimal.