How to dynamically pull data from Google Insights for Search into excel using macro?

[spencerwalsh]

Hello,

I am new to VBA coding and have a question. I would like to pull data from Google Insights for search into excel using a macro.

A similar example would be pulling historical stock data from Yahoo Finance using excel. You would type in the stock ticker, start date, and end date into excel. The macro would then develop the dynamic url, and the pull the .csv file generated from Yahoo Finance.

I would like to do the same with Google Insights for search.

To walk you through the steps, go to:
http://www.google.com/insights/search/

Type in "chicago" in the search bar and hit search.

It generates the URL : http://www.google.com/insights/search/#q=chicago&cmpt=q

I the upper right hand corner there is a CSV file with the historical data of the search volume for the word "chicago". This is the file that I want excel to pull.

I have already figured out the url to automatically download the csv file:

http://www.google.com/insights/search/overviewReport?q=chicago&cmpt=q&content=1&export=2

What VBA code would allow me to just type "chicago" into a cell, run the macro, and have that data automatically pulled into excel? I have previously written the code for the Yahoo Finance example I gave above, but when I try to adjust it to use Google Insights it will not please.

Please let me know if you have any ideas. Your help is greatly appreciated. Thanks!

 

[spencerwalsh]

Any ideas?

 

[cojotheo]

Hello,

I am working on the same thing at the moment. I was initially thinking of a way to download the page(html) and then go through the data, but it seems your idea saves a lot of hasle and imports everything in excel.

I'll be working on this idea as well and update you if I make any progress ( although I am new to VB as well).

How did you manage to make it work from yahoo finance? ( what's the VB code for it?)

Many thanks!

 

[cojotheo]

One reason might be that, google insights has the login screen in it, whereas yahoo finance does not.

There are some people discussing about it:
http://www.business-spreadsheets.com/forum.asp?t=103

but I'm trying to get my head around it as well.