Load Web Data into Excel

-
Did you know that you can easily load data from a website into Excel? You can even do it with a Macro for sites you visit often. For example if you were tracking your investments or you were monitoring the stats for fantasy basketball. Collecting the data is 7 easy steps.

  1. Select Data from the menu
  2. Select Import External Data
  3. Select New Web Query...



  4. Enter the URL of the page that contains the data to import
  5. Excel will recognize tables. Select the table(s) that contains the data you want to import
  6. Select the Import button
  7. Confirm the first cell of where the data should be inserted


That's all there is to it. If you want to create a Macro, use the Macro Recorder (Tools menu) and repeat the steps. You can then add additional code if you want to import different sites into different sheets.
Happy data collecting!

Comments

Anonymous said…
Is there a way to pick outlook calendar and put it into a web application?
Wed Jan 17, 09:47:00 PM PST
Chris Todd said…
Check out this website: http://www.slipstick.com/calendar/olpubcal.htm. this would allow you to export your calendar to HTML. If you wanted something interactive, you would need to interface with Microsoft's Exchange Server OWA (Outlook Web Access). There are also 3rd-party providers such as OutShare (http://outshare.4team.biz/Default.asp) that may be able to assist.
Wed Jan 17, 09:56:00 PM PST
Unknown said…
Good article, thanks. But if it's possible to load data from page wich requires login?
Mon Oct 26, 06:27:00 AM PDT
CHRISdotTODD said…
I'm not 100% certain... it may vary by login type and/or Excel version. Try the procedure I suggest, and see if you can login first. If that doesn't work, try first logging into the site before going to Excel. I suspect one of these two ways will work in most cases.
Mon Oct 26, 07:35:00 AM PDT
Skac said…
Hi!
I know about Excel's ability to import tables from websites, what I'd like to know if it is possible to import data that are not in table format on the site. So for example if ther's a web game with research data layed out in 2 by 7 format, but not in table. Can this somehow be imported in excel and if yes, how?
Tue Nov 23, 05:52:00 AM PST
CHRISdotTODD said…
Have you tried just copy-n-paste? Sometimes that will work with one browser and not another, so it's worth trying with IE and Firefox. If that fails, give us the URL of where we can access the data, and perhaps someone can find a solution for you.
Tue Nov 23, 10:53:00 AM PST
Post a Comment

Popular posts from this blog

Digital Signatures in PDF Do Not Print

-
Image
Trouble getting the digital signature in a PDF to print? The solution is actually simpler than you might think. With your document open in Adobe Acrobat Reader, click File >> Print. When the Print dialog box opens, look for the "Comments and Forms:" drop-down below the Properties button, located in the upper-right. Click the drop-down and select "Document and Markups." Now print. That's all there is to it.
Read more

Referencing the value of a cell, not its formula

-
Image
In Excel, I will use formulas quite frequently for my analysis. At times I want to reference the resulting value of the formula for comparison or to feed another formula. Unfortunately, if you are doing a comparison, Excel will try to compare the formula instead of the formulas results. Well, turns out there is an easy way to correct this. If you want your formula result to be a number, then use the VALUE function. If you want the formula result to be text, use the TEXT function. Here is the syntax for each: VALUE(text) TEXT(value,format_text) -- The format_text value can be any format from the Category box on the Number tab (in the Format Cells dialog box) except General. Here is an example of each: =VALUE(MID(A2,46,FIND("&",A2)-46)) -- this actually finds a number beginning with the 46th character of a string, and continues until it finds the ampersand character. By wrapping the formula with VALUE, I can now compare the number to other numbers in my looku
Read more

CorelDRAW X4 Crash on Startup

-
I had a fresh install of CorelDRAW X4, and each time I tried to start it, it would crash. After some searching through Google, I found the following fix. Open the folder C:\Program Files\Corel\CorelDRAW Graphics Suite X4\Programs\UIConfig\CorelDRAW Edit DrawUI.xml Find the line <dockpage guidref="bc1e2f70-3b58-41cd-8406-aaa550482972" visible="true" selected="true"> Change visible="false" and remove selected="true" <dockpage guidref="bc1e2f70-3b58-41cd-8406-aaa550482972" visible="false"> Save and close DrawUI.xml Fold down F8 and restart CorelDRAW X4 When prompted to update settings, select OK That did the trick for me. Apparently it is caused by a conflict with MFC dlls that are installed (version 1833) with SQL2008.
Read more