*** 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 Ends ***

This is a long overdue update of the following post:

https://vinzjeannin.wordpress.com/2016/06/11/heatmap-cheat-cheat-usda-wasde-report-excel-vba-macro-automation-wheat-corn-soybeans/

Indeed, it concluded with: “It will need an update if USDA changes the format (unlikely to happen though)”. The past weeks, months, if not years have proven that the unlikely is never actually uncertain. USDA changed the whole structure of the hosting methodology of the WASDE report. Bugger!

And also, even if one should be able to update the release dates by themselves in the sheet ‘Param’, it will be the occasion to make sure the file will be downloadable as operational for 2020. Finally, it worth being mentioned that USDA has added a few bits and bobs like a ‘World Less China’ section, has moved Russia and Ukraine in ‘Major Exporters’, added Bangladesh, Nigeria and Japan as a major importer of wheat (but removed Pakistan and FSU totals), etc,… The tables have been modified accordingly. Quite a bigger update than I initially thought to be fair. Very much telling on how much I look at the report these days… It required to change a few line references in the macro.

The main structural change is actually pretty simple. The latest USDA WASDE report is not called ‘Latest’ it is now dated. So we just needed to update the file names and location to ensure our VBA program finds the file to open, in essence the current and the previous one.

It creates small challenges: ‘Latest’ was opening a file regardless of what month it was and was detecting the month and opening the previous one. We will now need to try to open the current month, but if it’s before publishing, like the on the 9th of May 2020 the May 2020 report is not available yet, it will have to open the April one as the latest. It cannot just be a date and time test as report may be delayed or so, so we’ll have to test the existence of the file at the expected link. We will just assume that on or after the day of release, the file is available, if it’s not, we will default to the previous month.

We’re adding the test in the sheet ‘Param’, checking for the current month and year and until the day before the day of the release the previous month is assumed and on the day of the release, the current month will be assumed. This is obviously the critical day, you don’t want to wait for the day after to ensure the program is not bugging! If the file doesn’t exist, we’ll shift our cell reference by -1.

The new layout is as follows and our key formula is as follows, in the column I:

=IF(G24=””,””,IF(AND(YEAR(G24)=YEAR(TODAY()),MONTH(G24)=MONTH(TODAY())),IF(DAY(TODAY())<DAY(G24),ROW(I23),ROW(I24)),””))

It returns the line number of the line showing the expected couple of current and previous file, and H4 will be used to shift the line by -1 if required on the day of the release.

It is relatively easy as if the unpublished file is getting opened, a page shows the following message: “This file is not yet available. Please check back after the scheduled release date/time.”. Meanwhile, if the a USDA WASDE report is actually getting opened, the cell A1 is something like “WASDE – 599”, we will just test the 5 characters on the left of the cell A1! If it’s “WASDE”, it’s a winner, if it’s not, we shift everything down one month! And we do not forget to initialise the shift to 0 obviously…

We’ll call this ‘FileTest’ routine before the Wheat, Corn, Beans and OldCrop routines are called.

Sub FileTest()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ThisWorkbook.Sheets("Param").Range("H4") = 0

WASDE = ThisWorkbook.Sheets("Param").Range("D4") & ThisWorkbook.Sheets("Param").Range("G4")
Workbooks.Open Filename:=WASDE

CellTest = ActiveWorkbook.Sheets(1).Range("A1")

If Left(CellTest, 5) <> "WASDE" Then
    ThisWorkbook.Sheets("Param").Range("H4") = -1
End If

ActiveWorkbook.Close (False)


End Sub

In our Wheat, Corn and Beans routine (calling in essence the current WASDE), it’s relatively straightforward, we just need to change the addressing to the current WASDE to:

WASDE = ThisWorkbook.Sheets("Param").Range("D4") & ThisWorkbook.Sheets("Param").Range("G4") 

However, while writing these lines, there’s an additional issue arising. USDA is only showing in a standard folder structure the two last files. However, USDA is considering the last file, for example as of the 9th of May, to be the unpublished May report. In other words, the previous one (April 2020) is considered as the previous and is the latest available on the standard file structure. The March 2020 report is considered as historical already.

And historical files are now archived with a dynamic link (non-static, non-permanent) making impossible to generate the link without using their API. Therefore, on the ‘OldCrop’ routine, we need to change the addressing of the files, for the previous one and the current one, as follows, but it will not work as long as the latest file is considered to be the one soon to be published. That’s a pain…

So, for the time being (we need to make it work for the May 2020 release), let’s do an error handling. If we can’t download the actual previous report, we’ll just bypass and zero the old crop data revision. We’ll still gather the actual Old Crop data from the latest report but we won’t be able to fill the table showing the movements (we will zero it indeed). However, on the day of the May 2020 release, and for the rest of the month of May 2020, this will be fully functioning. USDA is probably changing the latest report to the one to be published a few days before release, but we can assume that one won’t wait until early June 2020 to run the May 2020 data! So once it’s run on the day of the release, it done and dusted, fully updated, on the new crop and the old crop.

Our error handling bits of code… We firstly don’t want to open the file that doesn’t exist.

PthPrev = ThisWorkbook.Sheets("Param").Range("D4") & ThisWorkbook.Sheets("Param").Range("F4")

On Error GoTo BypassPrev

Workbooks.Open Filename:=PthPrev
Previous = ActiveWorkbook.Name

BypassPrev:
If Previous = "" Then
    MissPrev = 1
End If
On Error GoTo 0

Then, as the variable ‘MissPrev’ acts as a flag, we just duplicate the current value to the previous value if the said file failed to open. 

    If MissPrev = "" Then
PrevV = Workbooks(Previous).Sheets(PrevSht).Cells(PrevLine + Line, Col + 1) Else PrevV = CurVal End If

Well… On top of this I had forgotten the May report is showing the New Crop data for the first time, so the New Crop of an April report becomes the Old Crop of a May report, so previous month of New Crop is #N/A… So May Old Crop as to compare with April New Crop. Just a few if then else to deal with this.

But for the glory and because one cannot accept a spreadsheet to fail, we’ll investigate how to download historical files using the API.

https://usda.library.cornell.edu/apidoc/index.html

Indeed, one cannot be satisfied, if the file has not been maintained, to be unable to check the March 2020 to April 2020 Old Crop changes for a few days until the May 2020 report is  published. Of course, there’s also a solution to download the file March 2020 manually and save it on the computer and direct the program to it:

https://usda.library.cornell.edu/concern/publications/3t945q76s?locale=en

But a full automation needs to be… Fully automated…

But again, as long as 2 actual files are available from the WASDE home page, this will perfectly work!

USDA WASDE Homepage: https://www.usda.gov/oce/commodity/wasde/

We’ll hide for the moment the macro to export tables in JPG and to broadcast on Twitter until Old Crop issue is fully resolved by the API.

To be continued!

Download file here