Macro to run Javascript on webpage to download CSV data?

I am trying to download fundamental financial data on several stocks (automatically) using a macro. Specifically, I would like to download one or more tables of data from Morningstar, such as the Key Ratios data (for Microsoft [MSFT] for example) at: financials.morningstar.com/ratios/r.html?t=MSFT&region=USA&culture=en_US.

I thought I could use a QueryTable (such as when manually pulling data using -Data-, -From Web-), but when I try it manually I cannot access the data table (there is no yellow arrow next to it). However, there is an Export button on the webpage that apparently runs a Javascript routine [javascript:exportKeyStat2CSV()] to download a CSV file.

My question: Is there any way to program a VBA macro within Excel to access this webpage, execute the javascript routine, capture the resulting CSV data, and paste it into my existing worksheet (at a particular location)?

I am not a VBA expert, and I have no knowledge of Javascript at all, so please respond in simple terms and include sample code if possible. Thanks!

Originally Posted by Norie

You could automate IE from VBA, navigate to the page you want and then, using code, click the button.

That should trigger the code behind the button, getting the data into Excel really depends on what actually happens when the button is clicked.

Does the data appear on a web page?

Are you prompted to download a file?

Thanks for the response! I am running Chrome as my default browser, not IE. Clicking the button automatically downloads the file (to my Downloads folder) with no prompts at all. Apparently that is due to settings within Chrome, since I just tried the same thing using IE and got a prompt (to Open or Save the file). So, where should I go from here?

I answered you on Stack Overflow

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Originally Posted by Kyle123

I answered you on Stack Overflow

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Kyle,

I did not post this (or any similar) question on StackOverflow.com, so I'm not sure how you would have answered me there. Can you provide a link to the post you answered? I can't find any post on StackOverflow (from me or anyone else) covering this question. Thanks...

This is the ONLY forum on which I have posted this question. If someone else has already posted the same question somewhere else (and had it answered) then I would appreciate a link to that information.

Apologies, the timing and request are incredibly similar, please see the link

http://stackoverflow.com/questions/3...24297#30324297

Wow! I can see why you thought it was a duplicate posting! Thanks for the link!

And now (assuming you're the same Kyle that answered the post in StackOverflow) a few questions (which will likely display my ignorance), if you don't mind:
• It looks like the private function references a library "urlmon". Is that built into Excel (or VBA)? Or do I need any other code / steps to access that library?
• How do you find the URL string to use as an input for the function? I know the URL for the webpage, and I know the name of the javascript routine, but how do you combine the two into the single URL you used? What are the rules for structuring the combined URL?
• Looks like the URLDownloadToFileA function saves the resulting file (as szFileName). I know how to use QueryTables to load a CSV file directly into a worksheet (if I have a URL that links directly to that CSV file). Is there any way to combine the two, to download the "javascript-triggered" CSV directly into my worksheet? Or must I always save the CSV file first, and then use VBA to open that file and copy its contents into my worksheet?

Thanks for all your help....

Sorry, forgot to reply!

I am indeed, I get about a bit

The Function calls what's known as an API, it;s just a windows function that isn't included in VBA. It just means that it will only work on Windows computers, not on Macs - but it should work on all PCs by default.

Finding the correct url takes a bit of detective work, essentially all that happens when you click the button is that it calls another page with the right parameters to generate the csv you want - so all we need to do is check out the request made when we click the button. This is dead easy to do since all modern browsers have the tools built in to do it (usually by pressing F12) - I use Chrome and you can see the results below.

The easiest way to get it into your spreadsheet is to simply go to Data > From Text, when the dialog pops up asking you to choose a text file, just paste:
http://financials.morningstar.com/aj...cur=&order=asc
Don't worry that it's a url, Windows will deal with the download.

The text import wizard then pops up, choose comma delimited and where you want to put it then you have it on your sheet. You can even update it by Clicking refresh