The Currencies Data Type has recently been added to the gallery of available Data Types for users of certain versions of Excel. It allows for the display of the current exchange rate and other associated information for designated pairs of currencies. However, the feature shouldn’t be used by finance professionals.
A new ‘Currencies’ Data Type has just been added to Excel for Microsoft 365, Excel for Microsoft 365 for Mac and Excel for the web. The Data Types gallery in the Data Types group of the Excel Data Ribbon tab has been somewhat unpredictable, with new Data Types appearing with one update and then vanishing again with the next. The reason for this is now apparent. Microsoft has announced the imminent end of its agreement with Wolfram Alpha to provide the data service for the additional data types. From June 11th 2023, the service will no longer be available. This leaves three external Data Types: Stocks, Geography and now Currencies. The fourth type, Organisation, allows you to create your own Data Types.
The Tip of the Week series has previously covered the main types here:
and the Organisation Data Type here:
There is also an examination of how to use Organisation Data Types in the production of management accounts here:
The Currencies Data Type unsurprisingly allows for the calculation of exchange rates by linking to the Internet. The exchange rate pair is entered into a cell in the format: GBP/USD The Data Type is then applied to the cell by selecting it and clicking on the Currencies Data Type in the gallery:
Once the Data Type has been applied, an Insert Data icon will appear at the top-right corner of the cell whenever it is selected. Clicking on this icon displays a list of all the available data fields that can be extracted for the Data Type. Clicking on an item in the list will add the data for that field in the first available adjacent column:
Excel help recommends including the Data Type cell as part of an Excel Table to allow the inclusion of a heading row and to allow the field values to be copied automatically to any new rows. The values can be updated by clicking on the Refresh All command in the Queries and Connections group of the Data Ribbon tab. Here we have included columns for the Price and Last trade time fields:
Note that the inability of Excel to identify a currency abbreviation will trigger the display of the ? icon. Clicking on this will display the Data Selector pane. If alternatives are available, the appropriate one can be selected from this pane:
Useful as this feature might be, it’s worth taking note of the disclaimer. Clicking on the disclaimer link will open a pane with more details, including some specific usage restrictions:
“The data is not for financial industry professional use or use by other professionals at non-financial firms (including government entities) who perform the functions including but not limited to: financial, trading, investing, hedging, compliance, risk management, M&A, treasury, financial and economic research, financial and economic strategy and planning, financial and management consulting, payment processing, leasing and financing.”
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.