Goal: scrape data from ~300 web pages (of identical format), each with stock market data for a different symbol. I currently use VBA that uses Excel's built-in Web Query tool to loop through all 300 URLs, pulling the data from each as it goes. Problem is that it takes ~5 minutes, which I want to cut way down, if possible.
Best idea I can think of so far: I can use a batch file to batch open all 300 URLs in Chrome or Firefox tabs, and they all load completely in about 30-45 seconds. Once they're open in a browser window is there any way for me to direct Excel to grab the data from them? If I understand correctly, if the data is displayed, it exists SOMEWHERE locally on my cpu, right? E.g. even if it's just in a local temp or cache folder, right? So my thinking is that there must be a way to direct Excel to grab it from that local source, rather than have it perform a whole new web query, which as I wrote takes nearly 5 minutes.
(Another thought: even if the browser-displayed data doesn't exist locally such that i can direct Excel to fetch it from there, there are browser extensions I'm playing with that claim to save all data in open tabs to local HTML files...which would seem to be a very simple source to query...but creating those files adds minutes to the process....reducing the time savings I'm attempting to achieve, so hoping there's a way to stick to the ~45s time it takes to load all of the URLs in tabs, and have Excel fetch it directly from there.)
Thoughts?
Best idea I can think of so far: I can use a batch file to batch open all 300 URLs in Chrome or Firefox tabs, and they all load completely in about 30-45 seconds. Once they're open in a browser window is there any way for me to direct Excel to grab the data from them? If I understand correctly, if the data is displayed, it exists SOMEWHERE locally on my cpu, right? E.g. even if it's just in a local temp or cache folder, right? So my thinking is that there must be a way to direct Excel to grab it from that local source, rather than have it perform a whole new web query, which as I wrote takes nearly 5 minutes.
(Another thought: even if the browser-displayed data doesn't exist locally such that i can direct Excel to fetch it from there, there are browser extensions I'm playing with that claim to save all data in open tabs to local HTML files...which would seem to be a very simple source to query...but creating those files adds minutes to the process....reducing the time savings I'm attempting to achieve, so hoping there's a way to stick to the ~45s time it takes to load all of the URLs in tabs, and have Excel fetch it directly from there.)
Thoughts?