Excel For Mac Web Query Example With Login And Password

• Excel For Mac Web Query Example With Login And Password Page
• Excel For Mac Web Query Example With Login And Password
• Excel For Mac Web Query Example With Login And Password Free
• Excel For Mac Web Query
• Excel Web Query With Username And Password

Summary

Excel web query is an excellent way to automate the routine task of accessing a web page and copying the data on an Excel sheet. If you use web query, you can instruct Excel where to look (web page) and what to copy (tables of data). What this will accomplish is that Excel will automatically import the data onto a worksheet for you. Microsoft Query uses the data source to connect to the external database and to show you what data is available. After you create your query and return the data to Excel, Microsoft Query provides the Excel workbook with both the query and data source information so that you can reconnect to the database when you want to refresh the data.

Prerequisites

• Internet Explorer 10

Capabilities supported

• Basic
• Advanced
  • URL parts
  • Command timeout
  • HTTP request header parameters

Load Web data using Power Query Desktop

To load data from a web site with Power Query Desktop:

1. Select Get Data > Web in Power BI or From Web in the Data ribbon in Excel.

2. Choose the Basic button and enter a URL address in the text box. For example, enter https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States. Then select OK.

   If the URL address you enter is invalid, a warning icon will appear next to the URL textbox.

   If you need to construct a more advanced URL before you connect to the website, go to Load Web data using an advanced URL.

3. Select the authentication method to use for this web site. In this example, select Anonymous. Then select the level to you want to apply these settings to—in this case, https://en.wikipedia.org/. Then select Connect.

   The available authentication methods for this connector are:

   • Anonymous: Select this authentication method if the web page doesn't require any credentials.

   • Windows: Select this authentication method if the web page requires your Windows credentials.

   • Basic: Select this authentication method if the web page requires a basic user name and password.

   • Web API: Select this method if the web resource that you're connecting to uses an API Key for authentication purposes.

   • Organizational account: Select this authentication method if the web page requires organizational account credentials.

   Note

   When uploading the report to the Power BI service, only the anonymous, Windows and basic authentication methods are available.

   The level you select for the authentication method determines what part of a URL will have the authentication method applied to it. If you select the top-level web address, the authentication method you select here will be used for that URL address or any subaddress within that address. However, you might not want to set the top URL address to a specific authentication method because different subaddresses could require different authentication methods. For example, if you were accessing two separate folders of a single SharePoint site and wanted to use different Microsoft Accounts to access each one.

   Once you've set the authentication method for a specific web site address, you won't need to select the authentication method for that URL address or any subaddress again. For example, if you select the https://en.wikipedia.org/ address in this dialog, any web page that begins with this address won't require that you select the authentication method again.

   Note

   If you need to change the authentication method later, go to Changing the authentication method.

4. From the Navigator dialog, you can select a table, then either transform the data in the Power Query editor by selecting Transform Data, or load the data by selecting Load.

   The right side of the Navigator dialog displays the contents of the table you select to transform or load. If you're uncertain which table contains the data you're interested in, you can select the Web View tab. The web view lets you see the entire contents of the web page, and highlights each of the tables that have been detected on that site. You can select the check box above the highlighted table to obtain the data from that table.

   On the lower left side of the Navigator dialog, you can also select the Add table using examples button. This selection presents an interactive window where you can preview the content of the web page and enter sample values of the data you want to extract. For more information on using this feature, go to Get webpage data by providing examples.

Load Web data using Power Query Online

To load data from a web site with Power Query Online:

1. From the Get Data dialog box, select either Web page or Web API.

   In most cases, you'll want to select the Web page connector. For security reasons, you'll need to use an on-premises data gateway with this connector. The Web Page connector requires a gateway because HTML pages are retrieved using a browser control, which involves potential security concerns. This isn't an issue with Web API connector, as it doesn't use a browser control.

   In some cases, you might want to use a URL that points at either an API or a file stored on the web. In those scenarios, the Web API connector (or file-specific connectors) would allow you to move forward without using an on-premises data gateway.

   Also note that if your URL points to a file, you should use the specific file connector instead of the Web page connector.

2. Enter a URL address in the text box. For this example, enter https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States.

3. Select the name of your on-premises data gateway.

4. Select the authentication method you'll use to connect to the web page.

   The available authentication methods for this connector are:

   • Anonymous: Select this authentication method if the web page doesn't require any credentials.

   • Windows: Select this authentication method if the web page requires your Windows credentials.

   • Basic: Select this authentication method if the web page requires a basic user name and password.

   • Organizational account: Select this authentication method if the web page requires organizational account credentials.

   Once you've chosen the authentication method, select Next.

5. From the Navigator dialog, you can select a table, then transform the data in the Power Query Editor by selecting Transform Data.

Load Web data using an advanced URL

When you select Get Data > From Web in Power Query Desktop, in most instances you'll enter URLs in the Basic setting. However, in some cases you may want to assemble a URL from its separate parts, set a timeout for the connection, or provide individualized URL header data. In this case, select the Advanced option in the From Web dialog box.

Use the URL parts section of the dialog to assemble the URL you want to use to get data. The first part of the URL in the URL parts section most likely would consist of the scheme, authority, and path of the URI (for example, http://contoso.com/products/). The second text box could include any queries or fragments that you would use to filter the information provided to the web site. If you need to add more than one part, select Add part to add another URL fragment text box. As you enter each part of the URL, the complete URL that will be used when you select OK is displayed in the URL preview box.

Depending on how long the POST request takes to process data, you may need to prolong the time the request continues to stay connected to the web site. The default timeout for both POST and GET is 100 seconds. If this timeout is too short, you can use the optional Command timeout in minutes to extend the number of minutes you stay connected.

You can also add specific request headers to the POST you send to the web site using the optional HTTP request header parameters drop-down box. The following table describes the request headers you can select.

Import files from the web

Normally when you import a local on-premises file in Power Query Desktop, you'll use the specific file-type connector to import that file, for example, the JSON connector to import a JSON file or the CSV connector to import a CSV file. However, if you're using Power Query Desktop and the file you want to import is located on the web, you must use the Web connector to import that file. As in the local case, you'll then be presented with the table that the connector loads by default, which you can then either Load or Transform.

The following file types are supported by the Web Connector:

• HTML page
• PDF

For example, you could use the following steps to import a JSON file on the https://contoso.com/products web site:

1. From the Get Data dialog box, select the Web connector.

2. Choose the Basic button and enter the address in the URL box, for example:

   http://contoso.com/products/Example_JSON.json

3. Select OK.

4. If this is the first time you're visiting this URL, select Anonymous as the authentication type, and then select Connect.

5. Power Query Editor will now open with the data imported from the JSON file. Select the View tab in the Power Query Editor, then select Formula Bar to turn on the formula bar in the editor.

   As you can see, the Web connector returns the web contents from the URL you supplied, and then automatically wraps the web contents in the appropriate document type specified by the URL (Json.Document in this example).

See also

Making and Using Excel web queries

Web queries bring tables from the web directly into Microsoft Excel. These instructions explain how to make and use web queries for Microsoft Excel on your Mac.

Data in Web pages

Data in web pages can be displayed a variety of ways:

• In row and column tables that are HTML based
  
• As the result of a query
  
• As a picture
• Within a PDF file
  

Getting the data

Free samples - but now they're broken!

Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work.

• MSN MoneyCentral Currencies
  
• MSN MoneyCentral Major Indices
• MSN MoneyCentral Stock Quotes

See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.

Run a web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet.

There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

Depending on how long the POST request takes to process data, you may need to prolong the time the request continues to stay connected to the web site. The default timeout for both POST and GET is 100 seconds. If this timeout is too short, you can use the optional Command timeout in minutes to extend the number of minutes you stay connected.

You can also add specific request headers to the POST you send to the web site using the optional HTTP request header parameters drop-down box. The following table describes the request headers you can select.

Import files from the web

Normally when you import a local on-premises file in Power Query Desktop, you'll use the specific file-type connector to import that file, for example, the JSON connector to import a JSON file or the CSV connector to import a CSV file. However, if you're using Power Query Desktop and the file you want to import is located on the web, you must use the Web connector to import that file. As in the local case, you'll then be presented with the table that the connector loads by default, which you can then either Load or Transform.

The following file types are supported by the Web Connector:

• HTML page
• PDF

For example, you could use the following steps to import a JSON file on the https://contoso.com/products web site:

1. From the Get Data dialog box, select the Web connector.

2. Choose the Basic button and enter the address in the URL box, for example:

   http://contoso.com/products/Example_JSON.json

3. Select OK.

4. If this is the first time you're visiting this URL, select Anonymous as the authentication type, and then select Connect.

5. Power Query Editor will now open with the data imported from the JSON file. Select the View tab in the Power Query Editor, then select Formula Bar to turn on the formula bar in the editor.

   As you can see, the Web connector returns the web contents from the URL you supplied, and then automatically wraps the web contents in the appropriate document type specified by the URL (Json.Document in this example).

See also

Making and Using Excel web queries

Web queries bring tables from the web directly into Microsoft Excel. These instructions explain how to make and use web queries for Microsoft Excel on your Mac.

Data in Web pages

Data in web pages can be displayed a variety of ways:

• In row and column tables that are HTML based
  
• As the result of a query
  
• As a picture
• Within a PDF file
  

Getting the data

Free samples - but now they're broken!

Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work.

• MSN MoneyCentral Currencies
  
• MSN MoneyCentral Major Indices
• MSN MoneyCentral Stock Quotes

See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.

Run a web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet.

There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

• From the Data menu choose Get External Data > Run Saved query.
  
1. The Choose a Query dialog displays defaulting to the Queries folder.
2. Select a query file to run in the Choose a Query dialog.
3. Click the Get Data button.
4. The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
5. Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
   
• Click the Run Saved Query button. Because Microsoft forgot to put this button into the Ribbon, to use this option you must first add the Run Saved Query command to a toolbar Once you've added the Run Saved Query command to a toolbar:
1. Click the Run Saved Query button.
2. The Choose a Query dialog displays defaulting to the Queries folder.
3. Select the query file to run in the Choose a Query dialog.
4. Click the Get Data Button.
5. The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
6. Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.
• Run a query using a VBA (Visual Basic for Applications) macro
• Click here for instructions on how to display the Visual Basic Editor (VBE)
• Click here for an introduction to how to code using the VBE
• For an example, see heading below on this page: Web queries and Visual Basic for Applications (VBA)
  

To refresh the data from the web page, select any cell within the query results and then from the Data menu choose Refresh Data.

Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. 'Relative' hyperlinks and specialty hyperlink protocols will not work when clicked.

Excel For Mac Web Query Example With Login And Password Page

Make a simple web query

The simplest web query consists of the URL of a web page which has an HTML table saved as a text file. To make your web query, take the following steps:

1. Open Microsoft Word to a new, blank document
2. Type or paste the URL of the web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears and Keep Text Only from the pop-up menu.
3. Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name. Navigate to this location: Applications:Microsoft Office 2011:Office:Queries and then click the Save button.
4. The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
5. We're done with Word. If you want to he a purist, Take this optional step: in Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file. The web query file will work even if you don't change the file extension to .iqy and leave it as .txt.
   

Now you can run your saved web query as described earlier.

Make a dynamic (parameter) web query

Some web pages request input so that custom result sets can be delivered. You can see how such a web query is constructed by opening the files in Microsoft Word to examine the contents of the built-in web queries. When you run the web query MSN MoneyCentral Stock Quotes you are prompted to enter the code for a particular stock. If you use Microsoft Word to open the sample web query MSN MoneyCentral Stock Quotes you can see the text for that query. This query uses the formal structure for a web query. The question mark embedded after the URL triggers Excel to display a dialog box prompt for the web page, which is expecting a stock market SYMBOL parameter from a web browser. QUOTE is the name of the parameter (See Figure 3 below):

WEB

1

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=['QUOTE','Enterstock, fund or other MSN MoneyCentral Investor symbols separated by commas.']

Selection=EntirePage

Formatting=All

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

Here's another example of a dynamic web query. This simple is for Yahoo Finance where jasz is a stock market symbol:

http://www.finance.yahoo.com/q?f=jasx

This example also queries Yahoo, but this one puts the results into a single column

Excel For Mac Web Query Example With Login And Password

Working with query result sets

The result set querytable of a web query has many properties you can control. When you first make your web query, there are settings available to you in the Returning External Data to Excel dialog (see Figure 1).

Figure 1 - Returning External Data to Excel

Click the Properties button to display the External Data Range Properties dialog when you first make a query. You can also display this dialog by right-clicking into the querytable result set and choosing Data Range Properties from the pop-up menu. The options presented are context sensitive. Not all options are available for web queries.

Figure 2 - External Data Range Properties

If your web query is dynamic (requests a parameter), the Parameters button becomes available in the Return External Data to Excel dialog box. Click the Parameters button to display the Parameters dialog, (see Figure 2). In this dialog you can modify the text of the prompt, enter a value to always use, or choose a cell and use that cell's value to satisfy the prompt. The Parameters dialog can also be displayed by right-clicking in the querytable result set of a parameter query and choosing Parameters from the pop-up menu.

Note the option to 'Fill down formulas in columns adjacent to data.' Enabling this option lets you use calculated columns with the web query result set.

Figure 3 - Parameters

Web queries and Visual Basic for Applications (VBA)

You must use full URLs and they must be http:// type URLs. Here is a code example showing VBA syntax:

Sub MakeWebQuery()
With ActiveSheet.QueryTables.Add(Connection:= _
'URL;http://www.domainname.com', Destination:= _
Range('A1'))
.PostText = 'local'
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 1
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
End Sub

Use the following code to refresh a query. The selection cursor must be in the QueryTable before running this code. If a parameter query is refreshed, the user will be prompted for parameters.

Sub RefreshQuery()
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

Excel For Mac Web Query Example With Login And Password Free

Excel For Mac Web Query

Excel Web Query With Username And Password