Author: Gary Knott

STOCKHISTORY function

If you have Office 365 then you can use the function STOCKHISTORY. As the name implies, its main purpose is to enable you to retrieve stock prices, but it can also be used to retrieve historical exchange rates. This can be very useful e.g., for converting historical transactions to the reporting currency or assessing the volatility of foreign currency cash flows.

Exchange rates

In the screenshot you can see an example formula in cell B6. The arguments for the function (the bits inside the brackets) must include two ISO currency codes separated by a colon and a date range (dates from and to). Optionally you can specify the interval (0 = daily, 1 = weekly or 2 = annual) and whether you want opening or closing rates etc.

You only need to enter the formula once! It returns a dynamic array with column headers and as many rows of data as necessary. What is also great is that the values are so-called Formatted Number Values (“FNVs”) which means that Excel automatically formats the values with the correct currency symbol.

Stock prices and data type

You can, of course, also use this great function to get stock price values as the above screenshot showing historical Tesla stock prices demonstrates. The formula is basically the same but here the first input for the function is the stock ticker symbol, here TSLA for Tesla.

You can also define the cell with the ticker symbol as data type stock.

Now when you select the cell, you get an extra box with the option to add extra information such as price, 52 week high and 52 week low.

Data source

The source of all retrieved data is refinitiv, "an LSEG (London Stock Exchange Group) business, [and] one of the world’s largest providers of financial markets data and infrastructure."

More information

You can find out more about this function on the official Microsoft blog.

You can easily add icons, stickers, illustrations and more to your Excel file. You can use these to spice up your dashboards to show an icon next to key output figures such as “Bike sales”.

Simply follow the six simple steps shown in the screenshot.

1. Select Insert
2. Select Icons on the ribbon
3. Select Icons in the dialog box
4. Enter one or more search terms or select a category from those provided (not shown in the screenshot as they disappear as soon as you start to search)
5. Click on the icons you want
6. Click on “insert"

Each icon is generally available in two versions: unfilled (white) and filled (shown black).

Once inserted in your file, you can move them around and format them e.g. to change the colour. Select an icon and you get an extra ribbon "Graphics Format" with various options.

You can add a text box next to your icon with a description and another text box with a value. You can make this dynamic so that the value updates depending upon the current content shown on your dashboard: simply enter a formula for the text box in the formula bar = (cell with value). You cannot perform any calculations here (e.g. using IF), so perform these, if necessary, in the source cell.

Get more great tips by signing up for my Excel tips newsletter and also get access to free downloads.

Did you know? Excel now has a Quick Analysis tool.

⚡📈 Select some data and the Quick Analysis icon appears at the bottom right with lots of options organised in the following areas:

• Formatting
• Charts
• Totals
• Tables and
• Sparklines (mini charts in individual cells).
  
  

For example: If you have multiple columns to SUM, you can use the Quick Analysis tool like this.

1. Select your data
2. Select the Quick Analysis tool that appears (or use the shortcut Ctrl Q)
3. Select Totals
4. Select Sum

🤩 Check it out!

❌😣 A client wanted to calculate the percentage of sales for all P&L lines. Such a ‘common size P&L' is useful for comparisons e.g., of years or of different companies. His first formula in row 3 (see screenshot above) gave a correct result. But when he copied this down, the denominator in each formula referred to the row above and not the sales figure in row 2.

📗 This is because cell references in copied formulas are automatically amended by Excel e.g. to refer to cells in the same relative row or column. The technical term for this is relative referencing. Often this makes sense e.g. you want a copied SUM formula to always add up the values in the same column as the SUM formula. But sometimes (as in this example) this isn’t what you want.

✔🤑 Here the denominator in the formula should always refer to the sales value in row 2 (cell B2), so the formula must read = (cell with value)/B$2. The dollar sign in front of the row number 2 fixes it, so it does not change when you copy the formula (see screenshot above).

💡 You can fix a column or row or both by manually typing in the dollar sign at the appropriate place(s) in your formula. Alternatively simply press F4 when the cursor is in or next to the relevant cell reference. This fixes (puts a dollar sign in front of) both row and column. Press F4 again to fix just the row. Press F4 again to fix just the column. Press F4 again to remove the fixing completely as the following example sequence shows.

B2 ➡ $B$2 ➡ B$2 ➡ $B2 ➡ B2

You can remember this order as follows: both, row, column, none. With practice, it becomes second nature to press F4 twice to fix the row only or three times to fix the column only.

As a general rule for fixing: Fix as much as necessary but as little as possible. In the example, it is only necessary to fix the row. And always review the results to identify and correct any errors.

This is just one of many how2excel tips you will find in my best practice book: Avoid Excel Horror Stories available on Amazon.