Modelling tips and tricks: New data types and the FIELDVALUE function
Welcome to our series of applied tips and tricks in Excel for accountants and financial analysts. As a professional modeller, FCA and Excel MVP Liam Bastick, takes a look at two new data types.
Outstanding in its FIELD
There’s some exciting news for those using Excel 2016 on an Office 365 basis (ie not the “perpetual licence”). You might not have this initially, but don’t fret. According to Microsoft, this feature is being made available to users on a “gradual basis” over several months.
It will first be available to Office Insider participants and later to Office 365 subscribers. If you are an Office 365 subscriber and you want it is as soon as it becomes available, do ensure you have the latest version of Office at all times (keep those updates updated!).
Content seriesView full content series
So what’s the big deal?
For a start, you can now get stock and geographic data in Excel. All you have to do is type text into a cell and convert it into the ‘Stocks’ data type or the ‘Geography’ data type. These two data types are new, and they are considered linked data types because they have a connection to an online data source. That connection allows you to bring back rich information that you can work with and refresh. You know – the things you have wanted to do for years!
Here’s a peek at the two new data types:
Data type 1: Stocks
In this graphic, the cells with company names in column A contain the ‘Stocks’ data type. The ‘Stocks’ data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for price and change in price are getting extracted from the ‘Stocks’ data type in column A.
Data type 2: Geography
In this example, column A contains cells that have the ‘Geography’ data type. This data type is connected to an online source that contains more information. Columns B and C are again extracting that information. Specifically, the values for population and gasoline price are getting extracted from the ‘Geography’ data type in column A.
We are sure you can see how these data types might be useful. So how do you set it up? Assuming you have had your version of Excel 2016 updated, it’s as easy as, er, 1, 2, 3, 4, 5, 6…
Step 1: Type some text
That’s right: just type some text in cells. In this example, we wanted data based on geography, so we typed country names. However, you could also have typed province names, territories, states, cities, etc. If you want stock information, similarly type company names, fund names, ticker symbols, and so on.
Industry insightsView more
Step 2: Create a table
Although it's not required, it is recommended you create an Excel Table. This is so ranges may be extended readily and easily later, should you wish. Select any cell in your data and go to Insert > Table or use the keyboard shortcut CTRL + T. This will make extracting online information easier later on.
Step 3: Select some cells
Next, select the cells that you want to convert to a data type.
Step 4: Pick a data type
On the ‘Data’ tab, click either ‘Stocks’ or ‘Geography’.
Step 5: Icons appear
If Excel finds a match between the text in the cells and the online sources, it will convert your text to either the ‘Stocks’ data type or ‘Geography’ data type. You will know immediately if they have been converted since you will see the stocks and geography icons.
Step 6: Add a column
Click the ‘Add Column’ button, and then click a field name to extract more information, such as ‘Population’.
If you see the question mark symbol instead of an icon, then Excel is having difficulty matching your text with data in Microsoft’s online sources. Go back and review your data. Correct any spelling mistakes and when you press ENTER, Excel will do its best to find matching information. If this does not work, click the question mark and a ‘Selector’ pane will appear. Search for data using a keyword or two, choose the data you want and then click ‘Select’. That’s it!
How to write formulae that reference data types
You can use formulae that reference linked data types. This allows you to retrieve and expose more information about a specific linked data type. For example, consider the linked data type, Stocks, used in cells A2:A11 below. In columns B and C, there are formulae that extract more information from the ‘Stocks’ data type in column A, viz.
In this example, cell B2 contains the formula =A2.Price and cell C2 contains the formula =A2.Change. When the records are in a Table, you can use the column names in the formula instead. In this case, cell B2 would contain the formula =[@Company].Price and cell C2 would contain =[@Company].Change. The additional benefit is that these formulae would automatically copy down too.
Some tips for when you start playing with these two new data types:
- as soon as you type the dot operator (.) after a cell or column reference, Excel will present you with a formula AutoComplete list of fields that you can reference for that data type. Select the field you want from the list or type it if you know it
- data type field references are not case sensitive, so you can enter =A2.Price, or =A2.price
- if you select a field that has spaces in the name, Excel will automatically add brackets ([ ]) around the field name, eg =A2.[52 Week High]
- the FIELDVALUE function can also be used, but it is recommended only for creating conditional calculations based on linked data types.
This last point is a nice lead into the other new item.
The FIELDVALUE Function
You can use the FIELDVALUE function to retrieve field data from linked data types like the ‘Stocks’ or ‘Geography’ data types. There are easier methods for writing formulae that reference data types (see above), so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.
Similar to the new data types, this brand-new function is being made available to customers on a gradual basis over several days or weeks. It will first be available to Office Insider participants and later to Office 365 subscribers. If you are an Office 365 subscriber, make sure you have the latest version of Office or you may not get the update when it’s your turn.
The FIELDVALUE function syntax has the following arguments:
- value: this is the cell address, table column or named range that contains a linked data type
- field_name: this is the name or names of the fields you would like to extract from the linked data type.
The FIELDVALUE function returns all matching fields(s) from the linked data type specified in the value argument. This function belongs to the Lookup & Reference family of functions.
In the following basic example, the formula, =FIELDVALUE(A2,"Price") extracts the Price field from the ‘Stock’ data type for the “internationally-renowned” JM Smucker Co.
The next example is a more typical example for the FIELDVALUE function. Here, we’ve used the IFERROR function to check for errors. If there isn't a company name in cell A2, the FIELDVALUE formula returns an error, and in that case, displays nothing (""). However, if there is a company name, then we want the formula to retrieve the Price from the data type in A2 with =IFERROR(FIELDVALUE($A2,B$1),"").
Note that the FIELDVALUE function allows you to reference worksheet cells for the field_name argument, so the above formula references cell B1 for Price instead of manually entering "Price" in the formula.
Word to the wise
If you try to retrieve data from a non-existent data type field, the FIELDVALUE function will return the new #FIELD! error. For instance, you might have entered "Prices", when the actual data type field is named "Price". Double-check your formula to make sure you've used a valid field name. If you want to display a list of field names for a record, select the cell for the record and press CTRL + SHIFT + F2.
Happy playing with your new toys!
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...