How To Automatically Pull Data From the Internet Into My Microsoft Excel File
You may ask why you would want to do this, but tens of thousands of people do this on a daily basis. Many of our more active financial trading clients that work with our New York office in Manhattan’s Financial District do this many times a day, some doing it once a minute. This is one of the many tools they use to manage their financial portfolios. In fact when I used to be a day trader I built my own day trading applications in Microsoft Excel and Access.
Those that take this to the extreme are often day traders, option traders, futures traders, etc. When I traded options I had my system to automatically update for me and to print our the analysis. When you have money on the time, speed is of the essence, especially when your positions gaps down.
The image below is of a trader watching many markets.
Some of the reasons to pull data from the internet via automation.
- Download stock/bond/options/etc data for analysis
- Download real estate transaction data
- Download economic data
- Download product data for purchases
- Download statistical data
Question: Can I Automatically Pull Data From the Internet Into My Excel File?
Answer: The short answer is: yes! The Excel Consultant experts explain how you can upgrade from manual queries to powerful macros to optimize your spreadsheets.
One of the great benefits of our digital age is that vast quantities of information are quite literally at our fingertips. Type a few keywords into Google and you’ll likely be inundated with data points. Computerized technology has solved many of our problems, but it’s also created new challenges. For example, now that we have massive amounts of data available to us, how should we go about actually understanding and using it?
Microsoft Excel is an outstanding tool for storing, organizing, interpreting, and generating information. Our team of developers, consultants, and programmers has made it their mission to help individuals and business realize the full power of this software. After all, a spreadsheet can help you track your finances, streamline your schedule, chart your business’s inventory, and much more.
At Excel Consultant, our talented team has found that many people don’t make the most of Excel because they simply don’t know how to use it. Of course, the first step to comprehending and codifying data with Excel is getting the information you want to interpret into a spreadsheet. In the following blog, our Microsoft maestros answer one of our clients’ more common questions: “can I automatically pull data from the Internet into my Excel file?”
The Manual Option: Queries
Before we tackle the issue of automation, it’s important to understand what downloading online information to your Excel file actually involves. There is a way to manually pull data from the Internet into your workbooks (aside from just typing it in line by line).
Microsoft Office Support explains: “you can use a Web query to retrieve refresh-able data that is stored on your intranet or the Internet, such as a single table, multiple tables, or al of the text on a webpage.” This tool pulls the data from the webpage into an Excel workbook. Microsoft Office Support notes: “web queries are especially useful for retrieving data that is in tables or reformatted areas.” Queries are mostly useful for downloading data that is already organized and easily transplanted into a spreadsheet. The other downside to queries is that they do require some coding savvy to run, although Excel 2007 does offer some default/sample queries for you to work with.
If you need to pull online information that is recurring (for example, the number of likes on social media accounts for marketing spreadsheets) and you had to do so manually, you’d need to create a “web query parameter query” which “prompts for input every time that you run it.” This is a much more complex process, and you’ll need to program it each time you need to establish a new web query parameter query.
Ultimately, although it can be done, using queries to pull online data into an Excel spreadsheet can be quite challenging, involving dozens of steps, coding expertise, and significant effort. If you frequently need to download information from the Internet into your workbooks to run your business, manage your personal finances, or perform any other function in Excel, the hassle of coding queries could be needlessly costing you time, money, and effort.
A More Sophisticated Solution: Macros
Are you questioning queries? Do you spend hours trying to input Internet information into your workbooks? If you, like many of Excel Consultant’s clients are wondering if there is a better solution, the short answer to this prevalent problem is: “yes!” You absolutely can automatically pull data from the Internet into your Excel file. To do so, you need to go from manual to macro.
Macros are a higher-tech, high-powered version of queries, or, to be precise, any other Excel function. As Microsoft Office Support puts it: “if you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or set of actions that you can run as many times as you want.” Recorded using Visual Basic (a common coding language), macros are an exceptionally valuable Excel development tool. If you’re already mired in query code, you might as well upgrade to macros.
Better yet, the Excel Consultant team can create stunning, sophisticated macros to pull the exact information you need from any webpage you want and format it to your specifications. This worthwhile investment could make your life easier, help you become more productive, and take full advantage of everything Excel has to offer.
Automation in Action: My Trade Analyzer
The Excel Consultant team is confident that we can help you automatically pull data from the Internet into your Excel files because we’ve done it many times before. Perhaps the best example of our accomplishments in this area is the recent project we completed for My Trade Analyzer, a tool to help stock market traders track their investments.
In this digital age of business, most of not all stock market information has been digitized, and My Trade Analyzer innovator, Ken Guynes, realized that stock market investors could use a hand in downloading daily updates to their Excel templates. The only problem was, he couldn’t find a team that could help him realize his vision for this service—that is, until he discovered Excel Consultant.
We worked closely with Mr. Guynes for months to create comprehensive, customized macros in Visual Basic. He was thrilled with the end result, our stunning solution that automatically pulls data of all types on a variety of stocks from hundreds of sites and organizes it for optimal performance in each users’ spreadsheets, even generating personalized reports in the process. Check out our Case Study of My Trade Analyzer to learn more.
Contact Excel Consultant Today
Below is a simple example of the code needed to pull data from the intranet.
GetFileFromWebsite2()
Dim HttpReq As Object
Dim FileData() As Byte
Dim strPath As String
Dim FileNum As Integer
Dim iRslt As Integer
Dim strResponse As String
Set HttpReq = CreateObject(“MSXML2.ServerXMLHTTP”)
HttpReq.Open “GET”, “https://www2.census.gov/programs-surveys/demo/tables/fertility/time-series/household-income/income-table-a-2010.xlsx”, False
HttpReq.send
FileData = HttpReq.responseBody
Set HttpReq = Nothing
strPath = “C:\Data\Fennell”
If Dir(strPath, vbDirectory) = Empty Then
MkDir strPath
End If
FileNum = FreeFile
Open strPath & “\income-table-a-2010.xlsx” For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
End Sub