Closed Thread
Results 1 to 19 of 19

Google Trends; Automated download; Strange result

  1. #1
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Google Trends; Automated download; Strange result

    Hi,

    please copy this link into your browsers address bar: http://www.google.com/trends/explore#cat=1104

    Your are now able to download the visualized time series in csv format, by clicking the top right gear wheel looking button.
    I use the following code to do this via vba:

    Please Login or Register  to view this content.
    I you now open report.csv, you will see that it just contains the sites source code.

    By pasting http://www.google.com/trends/viz?q=e...&graph=all_csv into the browsers addressbar, report.csv shows the expected results.

    How do I build the url in a proper manner? I can't find any hints in the pages source code!

    You have to copy this code into vba editor, to make the above lines executable:

    Please Login or Register  to view this content.
    Thank you very much!
    Lloyd

  2. #2
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Google Trends; Automated download; Strange result

    Any ideas?

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Google Trends; Automated download; Strange result

    Because the link that produces the csv uses the title, not the id

  4. #4
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Google Trends; Automated download; Strange result

    How do I trigger the csv download via the id?

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Google Trends; Automated download; Strange result

    You don't, you do it by the title, the code I gave you before gets you both

  6. #6
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Google Trends; Automated download; Strange result

    Kyle,

    Please Login or Register  to view this content.
    your code produces a list looks like:

    Celebrities & Entertainment News | http://www.google.com/trends/explore#cat=184

    I don't see how to construct the url via the title!

    I build the following url: http://www.google.com/trends/explore...ainment%20News

    It is obviously the wrong syntax.

    Lloyd

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Google Trends; Automated download; Strange result

    PHP Code: 
    "http://www.google.com/trends/viz?q=" replace(range("a1").value,"&","%2C%")  & "&cmpt=q&graph=all_csv" 
    Would do it - you need to escape the & signs since they are reserved in urls

    So how is it the wrong syntax? - If you'd said originally that this is what you were trying to do it would have been much easier

  8. #8
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Google Trends; Automated download; Strange result

    Now I got it!!!

    Thanks Kyle!

  9. #9
    Registered User
    Join Date
    09-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Google Trends; Automated download; Strange result

    Lloyd,

    Would you mind sharing your final code please? I am working on a similar project to loop through Google's geographic codes but am running into trouble.

    Thanks

  10. #10
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Google Trends; Automated download; Strange result

    DAK,

    I don't have the code with me at the moment. I will upload some stuff in the coming days.

    Lloyd

  11. #11
    Registered User
    Join Date
    09-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Google Trends; Automated download; Strange result

    Thanks, Lloyd. In the meantime here's my code and where I am lost:

    Please Login or Register  to view this content.
    The macro sometimes comes to an error on the save to part, but I'm not sure if that has to do with the CSV files being empty or what. Any help is appreciated.

    Thanks,
    DAK

  12. #12
    Registered User
    Join Date
    09-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Google Trends; Automated download; Strange result

    Lloyd,

    I know you're busy running Goldman, but did you happen to find that code?

    Thanks

  13. #13
    Registered User
    Join Date
    09-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Google Trends; Automated download; Strange result

    Nevermind. I'm an idiot and forgot and & in front of my q= part of the code! It works now.

  14. #14
    Registered User
    Join Date
    01-16-2014
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Google Trends; Automated download; Strange result

    Hi,
    I am writing a VBA for downloading a csv file from google trend.
    I have tested the code in #1 .It runs but cannot return a proper csv file just like Lloyd said.
    I do not understand what Kyle said. What do title and id refer to ?

    I do not understand and cannot run the code in #6.
    What is the input and ouput of this code?
    When I run this, I face a Run- time error '-2147024891(80070005)'.

    I am new in vba and sorry for my stupid questions.

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Google Trends; Automated download; Strange result

    colemanhk,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  16. #16
    Registered User
    Join Date
    01-16-2014
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Google Trends; Automated download; Strange result

    really sorry about that

  17. #17
    Registered User
    Join Date
    10-07-2014
    Location
    bengali, Kurdistan
    MS-Off Ver
    2013
    Posts
    3

    Re: Google Trends; Automated download; Strange result

    could anyone please give us the final code?

    I am new on using VBA and I find it a bit tough to create a macro that will dowload google trends data straight to excel..

    Thanks

  18. #18
    Registered User
    Join Date
    10-07-2014
    Location
    bengali, Kurdistan
    MS-Off Ver
    2013
    Posts
    3

    Re: Google Trends; Automated download; Strange result

    could anyone please give us the final code?

    I am new on using VBA and I find it a bit tough to create a macro that will dowload google trends data straight to excel..

    Thanks

  19. #19
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Google Trends; Automated download; Strange result

    Pls read post#15
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1