How to use Range.ConvertToLinkedDataType in the xlwings API way

The ConvertToLinkedDataType method in Excel, accessible through the xlwings API, is a powerful feature for converting static data in a range into dynamic linked data types. This functionality is particularly useful when working with data types like Stocks or Geography, where Excel can pull real-time or updated information from online sources. By converting a range to a linked data type, you enable automatic data refresh and access to rich, structured data fields without manual updates.

In xlwings, this method is called on a Range object. The syntax is straightforward: range_object.api.ConvertToLinkedDataType(ServiceID, LanguageCulture). Here, range_object refers to the Excel range you want to convert, typically selected using xlwings methods like sheet.range() or app.range(). The api property grants access to the underlying Excel object model, allowing direct use of Excel VBA-equivalent methods. The method takes two parameters: ServiceID and LanguageCulture. ServiceID is an optional parameter that specifies the linked data service to use; for example, use "Finance" for stock data or "Geography" for geographic information. If omitted, Excel will attempt to auto-detect the service based on the data content. LanguageCulture is also optional and sets the language and culture for the data, such as "en-US" for English (United States). If not provided, it defaults to the application’s current language settings.

For example, suppose you have a list of company ticker symbols in cells A1:A5 of an Excel worksheet, and you want to convert them to linked stock data types to fetch real-time financial information. Using xlwings, you can achieve this with a few lines of Python code. First, ensure xlwings is installed and import it. Then, connect to Excel, specify the target range, and call ConvertToLinkedDataType. Below is a code snippet demonstrating this:

import xlwings as xw

# Connect to the active Excel instance or open a workbook
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets['Sheet1']

# Define the range containing ticker symbols (e.g., "MSFT", "AAPL")
target_range = sheet.range('A1:A5')

# Convert the range to linked data type for stocks
target_range.api.ConvertToLinkedDataType(ServiceID="Finance", LanguageCulture="en-US")

# Save and close if needed
wb.save()