Linked Data Types in Excel – Access Stock Quotes and Wolfram Data to Generate Advanced Spreadsheets

Microsoft has developed hundreds of linked data types in Excel in partnership with Wolfram and other online data sources. With these data types, users quickly generate sophisticated spreadsheets using expertly curated data. To make the process even easier, Microsoft has created a library of smart templates to give users a jump start.

For example, a marketing team adapting an ad campaign for various cities can compare demographic and economic statistics at a glance. Likewise, a teacher can create an interactive spreadsheet with constellation data that changes based on current time and location. Students click a constellation name to view additional details without leaving Excel.

Using Linked Data Types in Excel

Linked data types are dynamic elements inserted into a spreadsheet that link to reliable online sources such as the Wolfram knowledgebase. Instead of searching for the information and then copying and pasting into a spreadsheet, users add a linked data type. They can then auto-populate data fields in adjacent columns.

For instance, in the city comparison above, the user enters various city names into a spreadsheet. Then, with the city names highlighted, they click the Data tab and select Cities from the Data Types gallery. Once the data has been converted to a linked data type, users can:

• • View the associated data card – To view the data card, click the icon next to the data. For cities, this will include geographic information, population by age group and gender, crime statistics and so forth. To see the data’s source, scroll to the bottom of the data card.

 

 

• • Insert related data fields into the spreadsheet – Select the cell(s) with the data and click the Insert Data icon. A dropdown list of data fields appears. Click the desired data field to add that information to the next column. For example, with the city names highlighted, select “per capita income” to compare incomes between cities.
  • Refresh values for items that update frequently – For data types such as stocks or astronomy that update rapidly, refresh the values by clicking Refresh All on the Data tab.
  • Create data types from text – Highlight text in the grid and select the Automatic data type. Excel matches the data to linked domains and applies a data type or gives the user a choice of data types to select. For example, Excel will automatically convert “Labrador,” “Doberman” and “St. Bernard” to the dog breed linked data type.

More Linked Data Types in Excel

In addition to the Wolfram knowledgebase, Excel also links to data types from providers such as Bing, Power BI, Refinitiv and more. Data types include a wide variety of subjects, from stocks and currencies to geography, chemistry, universities, music, space and medical.

Powerful Templates Available

In addition to a growing list of over 100 data types, Excel also includes several easy-to-use templates that build on data types. For instance, one template helps prospective students compare universities. A relocation helper compares data from cities in the U.S. Other templates provide trackers for nutrition, fitness and even baby names.

Once users download a pre-configured template, they can customize the template according to their data analysis needs.

Easily Track Stock Quotes

For example, the investment tracker template uses the stock data type and the STOCKHISTORY function to help investors analyze stock performance. Users enter the stock and EFT names and the number of shares for each. Using data from Refinitiv, Excel gives a regularly updated view of change in value, trends, and other key data points.

Investors can view a dashboard with visuals of the investment portfolio, allocations, and snapshots of the top and bottom performers. Additionally, they can create a watch list of stocks not already in their portfolio. And they can run a side-by-side comparison of two stocks.

Tap Into the Power of Microsoft 365

Most linked data types in Excel require a Microsoft 365 subscription. To migrate to Microsoft 365 and take advantage of linked data types and other powerful Microsoft 365 features, contact the Microsoft experts at eMazzanti Technologies.

Download Article PDF