Excel For Mac Web Query Example With Login And Password

/ Comments off

See this article for other sources for historical stock quotes. Example 2: Get data from a published Google Spreadsheet.See my article Excel Stock Quotes in Excel to see how you can publish a Google Spreadsheet and then use a web query to get the data into Excel. Become a Web Query Wiz. Take a few minutes to think about what mundane or repetitive tasks YOU do on the internet, particularly.

Excel For Mac Web Query Example With Login And Password

When you have the query looking just the way you want it, save the Excel web query (an icon or button for saving the web query is in the 'Edit Query' window. When you save the new query, your '.iqy' file will include the options you have selected. Other Example Web Queries. Below are a few examples of.iqy files that you might try. If you want to import data you best look at Excel's web connection wizard, this is IE based but it will happily download any tables etc you want. Some of the other board members might show you how you could run a SQL query (or something) if you are accessing a database you have permission to use. I use Power Query to get data from Web as your steps in answer forum: Then I will get the result: What I did, I use Chrome as default browser and login the web with the password. Then I save the password in Chrome. Then I go to Excel and load data, first time I failed to get by using legacy From Web option. Advanced tactics 1. Customizing web query. Once you create a Web Query, you can customize it to suit your needs. To access Web query properties, right-click on a cell in the query results and choose Edit Query.; When the Web page you’re querying appears, click on the Options button in the upper-right corner of the window to open the dialog box shown in screenshot given below.

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.

By Jim Gordon, co-author of
Office 2011 for Mac All-in-One For Dummies

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
With Microsoft Excel 2011 for Mac you can grab data from row and column HTML tables. If a query result is presented as an HTML table, you can capture that too. You can't import data directly from pictures or PDF files. Only data arranged in row and column HTML tables can be imported using a web query. Other techniques (e.g. Optical Character Recognition, Adobe PDF tools, etc) not discussed on this page can be used for capturing data from pictures and PDF files.
Excel for mac web query

Getting the data

You can get HTML row and column data by running a web query. A web query is a text file saved with a .iqy file extension. A web query file contains the URL of the web page that holds 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

Excel for mac web query example with login and password page

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.

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.

Excel For Mac Web Query Example With Login And Password

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.']

Excel For Mac Web Query Example With Login And Password Free

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

http://finance.yahoo.com/d/quotes.csv?s=ORCL&s=CSCO&s=COHR&f=sl1

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

Query

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 Page




Excel For Mac Web Query


Excel For Mac Web Query Example With Login And Password Download