loading

Excel : Stocks and Geography

Are you needing an easy way to work this Stocks and Geography data types?

We have a few tips and tricks and its easy to use from within Excel.

Excel data types: Stocks and geography

Excel for Office 365 Excel for Office 365 for Mac Excel Online Excel Web App

You can get stock and geographic data in Excel. It’s as easy as typing text into a cell, and converting it to 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, interesting information that you can work with and refresh.

Video: https://bit.ly/2Qbn2np

 

Stocks

In the picture above, the cells with company names in column A contain the Stocks data type.

You know this because they have this icon “Building” before Company name,  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.

 

Geography

In this example, column A contains cells that have the Geography data type. The icon ” Map” before Country name indicates this.

This data type is connected to an online source that contains more information.

Columns B and C are extracting that information. Specifically, the values for population, and gasoline price are getting extracted from the Geography data type in column A.

  1. Type some text in cells. If you want stock information, type a ticker symbol, company name, or fund name into each cell. If you want geographic data, type a country, province, territory, or city name into each cell.
  2. Then select the cells.
  3. Although it’s not required, we recommend creating an Excel table. Later on, this will make extracting online information easier. To create a table, go to InsertTable.
  4. With the cells still selected, go to the Data tab, and then click either Stocks or Geography.
  5. If Excel finds a match between the text in the cells, and our online sources, it will convert your text to either the Stocks data type or Geography data type. You’ll know they’re converted if they have this icon for stocks “Building” and this icon for geography “Map”
  6. Select one or more cells with the data type, and the Insert Data button  will appear. Click that button, and then click a field name to extract more information. For example, for stocks you might pick Price and for Geography you might pick Population.
  7. Click the Insert Data button again to add more fields. If you’re using a table, here’s a tip: Type a field name in the header row. For example, type Change in the header row for stocks, and the change in price column will appear.

Note: If you see  instead of an icon, then Excel is having a hard time matching your text with data in our online sources. Correct any spelling mistakes and when you press Enter, Excel will do its best to find matching information. Or, click  and a selector pane will appear. Search for data using a keyword or two, choose the data you want, and then click Select.

 

Discover more data with cards

After you convert text into the Stocks or Geography data types, an icon will appear in the cell. Click the icon to see the card. The card reveals a list of fields and corresponding values. Depending on the data, there could be numerous field/value pairs that you can see and work with.

For example, in this picture the card for France is shown. Capital is one of the fields available for France. And Paris is the value for that field. Leader(s) is another field, and the leader names are the values.

If you want to see more field/value pairs, scroll down inside the card.

If you’re curious where the fields and values are coming from, you’ll notice the “Powered by” note at the bottom of the card.

 

Add fields with cards

You can also add fields from cards. With the card open, rest your cursor over a field. Then click the Extract button.

 

Write formulas that refer to data types

It is also possible to write formulas that use the values from the Stocks or Geography data types.

This can be helpful if your data is not in a table.

For example, type =A2 and then Excel’s AutoComplete menu will appear, showing you the available fields for “France.” You can also type a “dot”, for example: =A2. and that will show the menu as well.

Tips and tricks

A quick way to open a card

For Windows, press Ctrl+Shift+F5.

For Mac, press Cmd+Shift+F5.

A quick way to add a column

Press Alt+Shift+F10 to open the Insert Data menu. You can also press the Ctrl key. Then use your arrow keys to move to a field, and press Enter to choose a field.

How to refresh the data

The Stocks and Geography data types are considered linked data types. This is because they refer to an online data source. Once you convert text to a linked data type, an external data connection is established in the workbook. That way, if the data changes online, you can update it by refreshing it in Excel. To refresh the data, right-click a cell with the linked data type and click Data Type > Refresh. That will refresh the cell you selected, plus any other cells that have that same data type.

If you want to refresh all linked data types and all data connections that may be in the workbook (including queries, other data connections, and PivotTables), click Data > Refresh All or press Ctrl+Alt+F5.

 

Can I unlink the cells?

Yes. Select a cell with a linked data type, then press CTRL+C to copy it. Press CTRL+ALT+V, and then choose Values. This will paste the data type into the new cell, but the data won’t be refreshable.

Switch out the data

You can switch out the information for a Stock or Geography data type. Just right-click the cell and click Data Type > Change…. A pane will appear on the right. Search for the data you want, and then click Select to put that information in place of the original one.

Remove a data type

If you don’t want the Stocks or Geography data type anymore, just right-click the cell, and click Data Type > Convert to Text. The data type is removed, there’s no longer an online connection, and the value in the cell is converted to text. Keep in mind that if you convert the data type to text, any column or formulas that had extracted values from that data type will display the #FIELD! error.

 

Sorting and filtering tip

You can always sort and filter data in Excel by using the filter buttons on the column headers. (Turn on filter buttons by clicking Data > Filter.)

But here’s a tip for cells with data types: Click the filter button above the cells with icons. Then decide how you want to sort or filter. If you want to do so using the displayed name, keep Display Value selected under Select field. If you want to sort or filter by another field from the linked data type, select that field under Select field.

In this example, we selected the field Area. Excel then used that as a basis for the filter check boxes below, and displays the populations for the countries we have in the table. Also good to know: Excel will change the Number Filter menus depending on the field you select under Select field.

 

 

 

NEWSLETTER SIGN UP

Sign up to our monthly newsletter for the latest news and information!