Power Query offers many ways to get data to Excel. One of them is to Web Data import feature. Let’s understand how this works by importing world stock exchange closing data from Google Finance website.
[Related: Introduction to Power Query]
Importing web data in to Excel – Step by step tutorial
Note: You need Power Query for this tutorial. Install Power Query on Excel 2013 (how to) and continue reading.
- Open a blank workbook. Go to Power Query ribbon & click on From Web button. Enter the URL of the webpage from which you want to import the data.
- Your web page will be loaded in to Navigator pane. If there are any tables or other sections of the page that can be readily embedded in Excel, they will show up in the navigation tree structure. Hover on any table to see if that is the data you want. Once you identify what you need, click on Load to get this data in to Excel. If you want to pre-process the data before loading in to Excel, click on Edit.
- Let’s say you have taken Table 0, which contains stock market closing data around the world. When this is edited in Power Query window, it looks like this.
- As you can see, there are 2 problems with this data. (1) Column headers are missing (2) Column 3 should be splitted in to 2 columns.
- Renaming columns: Simply double click on column headers and write whatever header you want.
- Splitting a column: Select column 3 and Click on home > split column button in Power Query window. Specify the delimiter (in our case space should work) and click ok.
- Once column is spitted, our new set up looks like below. Column 3.2 needs further cleaning. We need to remove the brackets ( ).
- Removing the brackets: Select column 3.2 and click on Home > Replace values button. Replace ( with nothing. Repeat the replacement, but this time replace ) with nothing.
- Almost done. Our data is clean. Just change the column titles and we get this:
- Finally load this data to Excel by clicking on “Close & Load” button. Instantly, all this web data will be loaded to Excel as a new table.
How to refresh the imported data?
Simple. Do one of the below:
- Click on “Refresh all” button in Data ribbon of Excel
- Right click on Excel table with web data and choose “Refresh”
- Activate workbook queries pane (from Power Query ribbon) and refresh the query by clicking on the refresh icon at right.
Download Power Query web data – Example workbook
Please click here to download the workbook with Power Query web data extraction example. Right click on the query in workbook queries pane and edit it to understand the pre-processing steps better.
What awesome things can you do with web data in Excel?
Integrating your own data with publicly available sources can lead to interesting analysis situations. Power Query, Power BI & Power Pivot offer several ways to connect to web data (Facebook, Azure market place, Google Analytics etc.) and analyze it in Excel.
Have you tried importing web data to Excel? What has been your experience like? Please share your tips & thoughts in the comments section.
More ways to analyze web data in Excel:
Learn more about analyzing web data in Excel
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub