Excel tips: how to use the new data types feature

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and today we're going to cover a topic called data types. Now, this is a new feature in Excel. It's in the data ribbon, and there's a section, of data types.

There's four different data types. There's organisation. Now, I'm not going to cover that in this podcast. I will circle back to it at the end. Just be aware that it does involve Power BI. So that's the organisation data type Stocks.

This is what we're going to talk about today, and this allows you to import share prices and other information into Excel, as long as you've got an internet connection. Currencies is the third data type, which we're also going to have a play around with. and then there's geography, which I'll just touch on towards the end.

Okay, so what we want to look at is the stocks data-type. And the way to access it is to use a specific code. So just start with a blank Excel spreadsheet and just type in stock in cell A1, and then in sell A2, enter this code XASX:WBC. So the XASX is the stock exchange. The WBC is the ticket code for Westpac Banking Corporation, and you need to put the colon in between them.

Okay, so select that cell, which is A2, and then click on the stocks data type. So remember that's in the data ribbon, and that when you do, it will convert the cell into a data type. It puts a little icon on the left hand side. It's like a little bank icon, I guess, and it will have the whole name there. So Westpac Banking Corporation. And then that code will appear in brackets at the end.

Now, as soon as you do that, you'll get a disclaimer popping up just above where the formula bar is. I'll read it to you, “financial market information is provided as is and not for professional or trading purposes or advice”. And then there's a learn more link that you can, go off to the internet to learn more about it. So basically, Microsoft is just putting a disclaimer there about, about the information that is imported into Excel.

Okay, so we've got Westpac, let's go and create the Commonwealth Bank and then ANZ. So in the, you can copy the A2 down to A3 and also A4. And just in those two cells, just, enter the other codes. Commonwealth Bank, it would be XASX:CBA. And then for ANZ that one's really easy, it's just XASX:ANZ.

So that will then convert into, again, the data types, which is the whole name with the code on the end and the little icon on the left. Now, what we're going to do is we're going to convert those four cells into a formatted table.

So select one of the four cells, go to format as table on the home ribbon. This is on the home ribbon, click format as table. Just accept the range that it, uses, and then select a colour and click okay. And then that will convert those four cells into a formatted table.

Now, formatted tables have a lot of built-in functionality, and they work pretty seamlessly with data types so seamlessly that it looks like magic. So here we go. So what you can do is in cell B1, type the word price, and then hit enter and see what happens.

So what will happen is that it, the formatted table is going to expand to include column B as well, and it's going to put the price for each of the three stocks next to them. and that's because price is one of the fields of the stocks data type. And when you have a look at the formula in the cell, it'll say it'll be equals, it'll be square bracket at stock, close square bracket, full stop price.

Now, that's part of formatted tables. If you haven't looked at formatted tables, I have done, articles and a podcast on formatted tables in the past. Highly recommend you learn formatted tables in Excel because they work pretty seamlessly with a lot of Excel's functions and features.

So what that's doing is it means grab the, the code from the stock column, that's what the at stock are on the same row, and the full stock price means grab me the price from that data type. So the full stop is used to sort of import the field for the specific data type that you're working with.

And price, obviously is one of those fields. Now, when we're working with the, ASX we're working in Australian dollars, but you can import stocks from other stock exchanges. So let's see how we can do that. I'm going to bring one in from New Zealand and then one in from, New York.

So in the next cell, that'll be cell A5, enter XNZE:WBC. They've also got, Westpac and that should automatically, when you hit enter, that should automatically apply the data type to that next line. And then for the New York one, enter XNYS:IBM. So they will bring in the prices in the currency of that particular stock exchange. What you might want to do is convert those to Australian dollars. So what you can do is you can go to another sheet, and we can create a lookup table for the currencies, which again are part of, data types.

So in another blank sheet, I'm just going to enter exchange rates into cell A1 in cell A2. You need to enter the code, NZD, and then a forward slash, so that's the slash that's leaning forward, AUD, and then underneath that you can use, USD/AUD. Okay, so they're the two codes. So you can select those two. And then on the data ribbon, grab the, currencies data type, and that should convert it. Again, it uses the same symbol as the shares. It looks like a little bank icon. And funnily enough, the currencies and the stocks used to be in the same data type.

They've sort of split them out recently. So that's why the, I think the icon is still the same. Okay, so we can convert this into a formatted table as well. Another way to do that is to con use ‘control T’ that uses the, default colour. So press ‘control T’ when you've got, one of those three cells selected. and then just hit enter. And that will create the formatted table and again, just type in price in cell B1 that will create the prices for, which is the conversions between the, the two currencies.

So rather than go through the, the formulas on the podcast, which, can be a bit difficult in terms of, being able to explain what they're doing, what I'll do is I'll just explain the formulas. If you check out the show notes for the podcast, there's the companion video and there's also the companion, article, that you can look at on the, in the black website. What we need to do for the, to get the first three characters of the exchange rate, so NZD and USD is to use the left function, but not a standard sort of a left function.

So that's why you do need to look it up on the website, or the video to see, how you actually access what's called the ticker symbol for each of the exchange rates. Okay, so once you've created that, you'll have NZD and USD in a column, and then we can go back to our original table. We can type in the currency as a column heading, and when you press enter, the currency will pop in and that will have AUD for the first three, and then it'll have NZD and then USD for the last two. And they're the ones that we really want to convert. and again, I'm not going to go through the formulas, but we can do a lookup, basically.

So if the currency is AUD, then we would use the, the number one. So otherwise we'd use an next lookup to look up that code in the third column of that, currency table, the exchange rate table, to bring in the, the actual value, the conversion rate. and again, you can check out the formulas on the video or the companion article on the, INTHEBLACK website for those. And so once you've got the exchange rate coming in, you can just multiply that by the price, and then you'll get the value in Australian dollars.

Okay, so that's working with tables and the data types, which works really well. You can just use a data type by itself in an individual cell. and when you do, you can still access all of those, the field, so the price, you know, 52 week high, those sort of things.

And so the way you do it, if we just open up a blank worksheet, and you type in one of those codes, so I'll just use Westpac again. So XASX:WBC, and again, set it up as a data type on the data ribbon. So, if A1 has that in there. So that's, it has the data type in there.

All you need to do to extract anything from it is to use a formula like ‘=A1.’ and then you'll be shown a, a listing of all the entries. So for example, employees is one of the options. So if you select that, you'll see how many employees are employed by Westpac Banking Corporation.

One thing to note, when you use data types, there's a format that comes across as well. Now, funnily enough for the, the price, the format that it uses is the accounting format. Now, I'm not a fan of that particular format, because it puts the dollar sign on the far, left hand side of the cell, I prefer the currency format, which puts the do sign right next to the number. As an example for, currencies, when I put the employee's number in is actually comes in in the, with the comma separator in there. So it's got the comma for the thousand separator automatically applied to that particular cell. So data types come with a format.

Now another way to access the information is if you select the cell that has the data type in there. So once you've applied the data type, a little icon pops up next to it and you can click that icon, it says insert data. And again, it gives you a listing of all of those type of, entries that you might have. So a 52 week high, for example, you just click that and um, further along on the same row, it'll give you whatever you've selected.

Now, there's something else that you can access as well. It's called a data card. If you right click this time, so right click with the mouse, there's an option to show data type card. And when you do that, you pretty much get a summary of everything that's there. There's a scroll bar on the right hand side, so you can scroll all the way down and see all of the information for that particular stock code. Okay, so data types are really exciting and sort of, I want to circle back to organisation.

Organisation data types are driven by Power BI. So you can set them up in Power BI and then you can access them in Excel and you can set up data types for your products. So this might include things like weight, colour, price, cost, all of those sort of things. And you'll be able to access those via this data type functionality. But as I mentioned, it is driven by Power BI, so you need that installed on premises probably to, to access it.

I haven't used that, so I can't really comment on the organisation data type, but as I said, it does offer some exciting opportunities there. The other data type is geography, so that one there is a little bit more general, but you can get information on locations and states, and countries using that. So with geography, you need to be fairly specific because, location names do vary across the world.

I'm from Perth and there's a Perth in Scotland and there's a Perth in Western Australia. So you can, you need to be fairly specific. So for example, if I do type in Sydney, because there's a few Sydney’s around the place as well, but if I put New South Wales on the end or NSW on the end, and then you, select that cell and then do geography, it will give you, again, all of access to all of the entries there. So when you click on the icon, there's things like area, country, region, latitude, longitude, population, those sort of things.

So that's, part of the geography data type. So data types exciting new feature. I think they are developing. Hopefully we'll get some more data types as time goes by. You do need the internet connection, so just a reminder of that. And the organisation does require Power BI and some setting up that looks like it could be exciting. Okay, thanks for listening.

Garreth Hanley:

Thanks for listening to Excel Tips, A CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks To find out more about our other podcasts and CPA Australia, check out the show notes for this episode and we hope you can join us next time for another episode of Excel Tips.