Downloading dynamic data monthly

[mickeystanford_alumni]

Hi guys,

Happy to be back. Hope you all having a good start of Summer.

I am wondering if someone could help me on the following.
I am trying to download data from a website where basically first I have to select the data and then I can download it on excel/pdf etc. The website is: USDA/NASS QuickStats Ad-hoc Query Tool

I will need to automate and retrieve this data weekly/monthly and wondering if there would be any code to quickly do this. If for example I want to retrieve USDA/NASS QuickStats Ad-hoc Query Tool from this link

Also, might be impossible but in order not to have to go to the 1st link, then click on the desired products, year, etc...is there any way to put a vba code which automatically chooses the data I want? Impossible right?

Thanks a lot and hope my explanation was clear.

Mike

 

[mickeystanford_alumni]

By the way, just read my post and think I explained myself really badly.
First. The code I am already using is this:

Sub Macro1()

Dim thisWb, downloadWb As Workbook
Set thisWb = ActiveWorkbook

Set downloadWb = Workbooks.Open("http://quickstats.nass.usda.gov/data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C.csv")

downloadWb.Worksheets(1).Range("A1:U2613").Copy Destination:=thisWb.Worksheets(1).Range("A2")

downloadWb.Close

End Sub

Then, the real question is, every time I will have to upload the sheet with new monthly data, will I have to change the link on my code? Hope its now more clear.

Thank you guys

 

[mickeystanford_alumni]

Hi gents, I DO REPOST IN HERE AS I FOUND MY FIRST POSTS WERE SOMEHOW UNCLEAR. APOLOGIES FOR DUPLICATING CONTENT.

Happy to be back. Hope you all having a good start of Summer.

I am wondering if someone could help me on the following.
I am trying to download data from a website where basically first I have to select the data and then I can download it on excel/pdf etc. The website is: USDA/NASS QuickStats Ad-hoc Query Tool

I will need to automate and retrieve this data weekly/monthly and wondering if there would be any code to quickly do this. A python code I could use is this:

data = {

"key": api_key,

"source_desc": "SURVEY",

"commodity_desc": commo,

"year": year,

"short_desc": items,

"agg_level_desc": "COUNTY",

"format": "JSON"

}

r = requests.get("http://quickstats.nass.usda.gov/api/api_GET/", params = Dat

a)

BUT I WANT TO DO IT WITH VBA

For the moment, I have used this code, but this forces me to automatically pick the product, source, year etc, and then ‘get data’, and then copy the link into my code.

Is there any way to do that automatically?

THANKS A LOT.


Dim thisWb, downloadWb As Workbook
Set thisWb = ActiveWorkbook

Set downloadWb = Workbooks.Open("http://quickstats.nass.usda.gov/data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C.csv")

downloadWb.Worksheets(1).Range("A1:U2613").Copy Destination:=thisWb.Worksheets(1).Range("A2")

downloadWb.Close

End Sub

 

[Colo]

Hi Mickey,

Actually, I was about to do it via using Internet Explorer, but it's really an old style, and not sure when Microsoft makes IE function not available.
So I decided to try using their API.
Please give this a try.

Sub GetCsvViaApi()

'Tools > Reffer > Microsoft WinHTTP Services, version X.X (my case vers 5.1)

'https://quickstats.nass.usda.gov/api
'Request API key then replace it with the following sample key.
'Note, the sample API key will not be available soon.

    Const key As String = "FD34620D-962B-3862-9B3D-D431625C8EDE" 'sample API key 
    Const FILE_NAME As String = "C:\Temp\YourData.csv" 'Need to prepare a folder named Temp in C drive
    Dim intFF As Integer    'free file
    Dim wbCSV As Workbook

    Dim Req As WinHttpRequest
    Set Req = New WinHttpRequest

    'Set parameters. Have a look at "Usage" in the link for api
    Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&commodity_desc=CORN" & _
                    "&year__GE=2010" & _
                    "&state_alpha=VA" & _
                    "&format=csv"

    Req.send

    intFF = FreeFile

    Open FILE_NAME For Output As #intFF
    Print #intFF, Req.responseText
    Close #intFF
    
    Set wbCSV = Workbooks.Open(FILE_NAME)
    
    'you can do whatever you want.

End Sub

 

[mickeystanford_alumni]

Hi Mickey,

Actually, I was about to do it via using Internet Explorer, but it's really an old style, and not sure when Microsoft makes IE function not available.
So I decided to try using their API.
Please give this a try.

Sub GetCsvViaApi()

'Tools > Reffer > Microsoft WinHTTP Services, version X.X (my case vers 5.1)

'https://quickstats.nass.usda.gov/api
'Request API key then replace it with the following sample key.
'Note, the sample API key will not be available soon.

    Const key As String = "FD34620D-962B-3862-9B3D-D431625C8EDE" 'sample API key
    Const FILE_NAME As String = "C:\Temp\YourData.csv" 'Need to prepare a folder named Temp in C drive
    Dim intFF As Integer    'free file
    Dim wbCSV As Workbook

    Dim Req As WinHttpRequest
    Set Req = New WinHttpRequest

    'Set parameters. Have a look at "Usage" in the link for api
    Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&commodity_desc=CORN" & _
                    "&year__GE=2010" & _
                    "&state_alpha=VA" & _
                    "&format=csv"

    Req.send

    intFF = FreeFile

    Open FILE_NAME For Output As #intFF
    Print #intFF, Req.responseText
    Close #intFF
   
    Set wbCSV = Workbooks.Open(FILE_NAME)
   
    'you can do whatever you want.

End Sub

Hi Colo, thank you so much for the time spent trying to figure out the code.
It seems correct actually, although API's were kind of new for me.

Though, I am getting a compile error: user-defined type not defined for the ''Dim Req As WinHttpRequest

1. Do you know what could be the problem?

Tks again

 

[Colo]

Hi,
You need to set a reference to Microsoft WinHTTP Services in your VBA Project (Tools -> References).
Have you done that?

 

[mickeystanford_alumni]

Ahaaa, I clicked on the wrong reference lol.
Wow, amazing. It does work indeed.

One more question if I can now that I got excited: if I want to do different commodities, like take a bunch of different ones, with different years, states for each etc, how could I add those into one single code?
For simplification, I will not take the state, it will always be National. But for example, same as Corn I will take Wheat for example. So putting both into the code?

Again, OUTSDANDING HELP. Appreciate it so much.

 

[Colo]

It was just a sample code, so you need to adjust it to suit your needs. This time, I wrote all the arguments (parameters) in the code. This kind of way is called a "hard cording". It's a handy way when always using the same arguments. In your case, I'd recommend making it a "soft coding" which gets resources from the outside of the code. Since Excel is a spreadsheet application, we can have a list on the worksheet. A simple way is that you can write parameters in cells and then use them from the code. (It's just my idea.)

In the code, there are some lines for setting parameters as follows.

"&commodity_desc=CORN" & _
"&year__GE=2010" & _
"&state_alpha=VA" & _
"&format=csv"

Instead of that, you can use the value from cells.
For example, it's going to be like this...

Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & Key & _
                    Sheets("control").Range("A1").Value

 

[Colo]

I've been waiting if someone to post another solution using like a Power Query that I'm still not good at in that area.
Come on! Experts!

 

[mickeystanford_alumni]

It was just a sample code, so you need to adjust it to suit your needs. This time, I wrote all the arguments (parameters) in the code. This kind of way is called a "hard cording". It's a handy way when always using the same arguments. In your case, I'd recommend making it a "soft coding" which gets resources from the outside of the code. Since Excel is a spreadsheet application, we can have a list on the worksheet. A simple way is that you can write parameters in cells and then use them from the code. (It's just my idea.)

In the code, there are some lines for setting parameters as follows.

"&commodity_desc=CORN" & _
"&year__GE=2010" & _
"&state_alpha=VA" & _
"&format=csv"

Instead of that, you can use the value from cells.
For example, it's going to be like this...

Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & Key & _
                    Sheets("control").Range("A1").Value

Hi Colo,
The question in here was more, as I tried but didn't really worked, on how to do for example:

Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&source_desc=SURVEY" & _
                    "&commodity_desc=CATTLE" & _
                    "&statisticcat_desc=PRODUCTION" & _
                    "&agg_level_desc=NATIONAL" & 
                    "&year_=2020" & _
                    "&format=csv"

and then to the same code adding:

Req.Open "GET", "http://quickstats.nass.usda.gov/api/api_GET/?key=" & key & _
                    "&source_desc=SURVEY" & _
                    "&commodity_desc=CHICKENS" & _
                    "&statisticcat_desc=PRODUCTION" & _
                    "&agg_level_desc=NATIONAL" & _
                    "&year_=2020" & _
                    "&format=csv"

So having cattle and chickens in the same code.
Also, not sure what the year adds, as I have tried also frequency periods but at the end it takes all of them...weird but ok not a hassle.

Thank you.