Web Data Connection Wizard

The SaveToDB add-in for Microsoft Excel enables you to retrieve data from web data services and save changes back using REST APIs, including OData.

Additionally, SaveToDB can extract data from HTML, XML, JSON, and CSV web pages. It also supports merging loaded data into databases.

SaveToDB supports various authorization methods: Windows, Basic, Forms, OAuth 1.0, and OAuth 2.0.

Below is the main dialog box:

Dialog Box Controls

URL

Use this field to enter a URL.
You can typically copy it from a browser.
SaveToDB allows configuring URLs. See below for details.
When you change the URL, the add-in attempts to discover other required parameters in the background.
If it fails to discover the parameters or you need different values, specify them manually.

Service URL

Enter the root address of a website or service here.
Service URLs are clearly defined for OData web services only. The service URL is the root URL that returns the service document.
For other web services and websites, the service URL should point to any protected area.
A website may have multiple protected areas, and the root URLs of these areas can serve as service URLs.
In most cases, the add-in automatically detects the root address for all web resources.
If the service URL is incorrectly defined, you may be prompted for authorization for multiple URLs from the website.

Authorization

Select an authorization method or a configured provider from this field.
SaveToDB usually detects these methods or providers automatically.

Edit button (...)

Click this button to open the dialog box for configuring OAuth1, OAuth2, or custom authorization settings, depending on your selected authorization method.

Save in Cache

Check this box to store the authorization settings in an encrypted cache file.
This option allows you to share your authorization settings across multiple Excel workbooks.
If unchecked, the add-in saves the settings only in the active workbook.

Accept

This optional field allows you to set the Accept header.
If the web server returns an error with the default header, try setting this field manually.

Referrer

This optional field allows you to set the Referrer header.
If the web server blocks the request, try setting this value to the website page URL (e.g., MSN Money).

UserAgent

This optional field allows you to set the UserAgent header.

Parameters

Use this optional field to set parser parameters.

Configure REST

Click this button to open the REST Settings dialog box.

Open Cache...

Click this button to open the connection string cache.
You can edit, add, or delete cached connection strings.
Note that the add-in encrypts connection strings using the Microsoft Cryptography API.

Test Connection

Click this button to test the connection.
The add-in performs checks in the background but does not display error messages.
This button connects and will display a message if an error occurs.

Help

Click this button to open the user guide.

Cancel

Click this button to close the dialog box.

Back

Click this button to return to the previous step of the wizard.

Next

Click this button to proceed to the next step of the wizard.
Additional steps appear if the web service supports selecting objects, such as OData.

Finish

Click this button to complete the wizard and insert a query into Microsoft Excel.
This button is enabled when the add-in successfully connects.
If the button is disabled, click the Test Connection button to understand the connection error.

Configuring URLs

The SaveToDB add-in automatically detects HTTP request parameters and displays them on the ribbon.

For example, consider the following query with parameters like symbols, chartType, isEOD, isCS, and isVol:

https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols=AAPL&chartType=1y&isEOD=True&isCS=true&isVol=true

You can override parameters using the {<Parameter name>=<Default value>} format.

For the above query, you can simplify it to use a single Symbol parameter for the ticker:

https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols={Symbol=AAPL}&chartType=1y&isEOD=True&isCS=true&isVol=true

REST API-based web services return structured results that are easy to extract automatically.

For other web pages and undocumented web services, the add-in employs intelligent algorithms to extract data.

You can configure the output using special parser parameters described below.

In the example above, the data resides in the Chart.Series JSON node.

Thus, the URL may include the rootpath=Chart.Series parameter, separated by a semicolon:

https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols={Symbol=AAPL}&chartType=1y&isEOD=True&isCS=true&isVol=true;rootpath=Chart.Series

Executing this request retrieves historical stock prices from the MSN Money website.

The columns from the JSON response will include: T, Op, Hp, Lp, P, and V.

SaveToDB 10 allows using SELECT commands for HTTP requests and supports name aliases.

For instance, a query might look like this (the request URL is in square brackets):

SELECT T AS Date, Op AS Open, Hp AS High, Lp AS Low, P AS Close, V AS Volume FROM [https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols={Symbol=AAPL}&chartType=1y&isEOD=True&isCS=true&isVol=true;rootpath=Chart.Series]

As a result, you'll get a table with columns like Date, Open, High, Low, Close, and Volume, along with the Symbol parameter on the ribbon to change the ticker.

As mentioned earlier, you can save downloaded web data to databases, including using SaveToDB methods with macros.

You can also leverage Excel features such as inserting and adding new formula columns, sorting, and conditional formatting.

Selecting OData Object

When connecting to OData web services, the wizard includes an additional step to select an object.

OData containers resemble database query list views.

You can use the query list to select a container or an object schema, and then connect to a container object.

Specifying Parameters

If the query includes parameters, including FunctionImport parameters for OData, the wizard provides an additional step.

For example:

You can enter parameter values and select which parameters you want to display on the ribbon in the right column.

Parser Parameters

The SaveToDB add-in extracts data from HTML, XML, JSON, CSV, and plain text pages using built-in smart parsers.

You can customize parsers using the following parameters. Separate multiple parameters with semicolons.

AddRowNum=true

Set this boolean option to add a row number column to the output.

ApiResult=true

Use this boolean option when a web service returns REST API JSON results.
This option disables special smart parsers that extract table data from irregular JSON.

AsIs=true

Set this boolean option to suppress any special processing of the input data.
For example, you can prevent converting Yahoo timestamps to datetime values.

CapitalLetter=true

Set this boolean option to capitalize the first letters of headers (JSON only).

CollapsedNodes=<node>[,...]

Use this option to include XML child node values into the parent node.
For example, given the following XML structure:

<passages>
<passage><hlword>Yandex</hlword> - the search engine that can...</passage>
</passages>

To create a single column for passages, use:

collapsedNodes=passages

FirstRow=<number>

Use this option to specify the first row of the source data to output.

FirstRowHasNames=true|false

Set this boolean option to indicate whether the source data has headers.

IgnoredTags=<tag>[,...]

Use this option to prevent creating columns for specified XML tags.
For example, given the following XML structure:

<passages>
<passage><hlword>Yandex</hlword> - the search engine that can...</passage>
</passages>

To ignore the hlword tag, use:

ignoredTags=hlword

NoSourceHeaders=True

Set this option to suppress using HTML table headers as column names.
This is useful for importing financial statements with dynamic column headers.
When this option is enabled, the add-in creates columns with names like F1, F2, etc.
This option is the opposite of the firstRowHasNames option.

Pages=<number of pages>

Use this option to specify the number of loaded pages (e.g., the number of option chain pages).
For example:

pages=20

This option works if the add-in can detect next page URLs.

RootPath=<root path>

Use this option to set the root element for output.
Here are several examples for XML, JSON, and HTML sources:

rootPath=.InvoiceTransmission.Invoice
rootPath=calls,puts
rootpath=*.*.timestamp,*.*.*.quote,*.*.*.adjclose
rootPath=1

Without this option, the add-in automatically detects output columns.

SaveToDB supports multiple roots with JSON and HTML, and a single root with XML.

RowValues=True

Set this option to output XML and JSON values in rows.
This is useful for exploring document structures.

SkippedNodes=<node>[,...]

Use this option to exclude XML or JSON nodes from the output.
You can specify column paths like parent1.parent2.column or suffixes like .column.
For example, to exclude columns with data types defined in the type attributes, use:

skippedNodes=.type