How to track sector performance in the stock market – Excel template

The image above shows the performance across industry groups for different date ranges, conditional formatting makes the table much easier to read. Press with left mouse button on the image to see a larger version.

I will in this post demonstrate how I built it and there is also a link to the workbook. The worksheet is easy to customize, you can add individual stocks och indexes that you find interesting.

The whole table refreshes automatically no need to adjust anything, however, Excel 365 is needed. The STOCKHISTORY function is used in order to fetch historical stock prices.

There are arrows next to each column header allowing you to sort the column you find most interesting.

Why track sector performance?
Every new bull market (a growing economy) has one or a few more sectors that advances quicker than the remaining sectors. You want to own the best performing stocks in these leading sectors. The table above lets you identify these sectors when the market turns up.

1. Add sectors, names, and tickers to the worksheet

Column A contains names of ETFs that track a sector, column B the corresponding ticker.

Select all tickers in column B.

Go to tab "Data" on the ribbon.

Press with left mouse button on "Stocks" button, see the image above.

The tickers change and a "stock" icon appears next to the name in column B. A new button appears, see the image above.

Press with left mouse button on the button, a popup drop-down list appears.

Scroll down until you see "Ticker symbol", press with left mouse button on the "Ticker Symbol". See the image above.

The table expands to the right, tickers are now visible in column C.

Press with left mouse button on the button again to see the popup drop-down list, press with left mouse button on "Price", and see the image below.

The table expands once again, current prices are now shown, or at least the last price. We will use the price value and the STOCKHISTORY function to calculate performance later in this article in section 3.

Press with left mouse button on the button again and now select "Change (%)".

A new column (E) appears showing the change in percent, see the image below. The percentage shows how the sector is doing right now if the stock market is open.

Add column header names for "Price" and "Change (%)".

Back to top

2. Calculate dates for each date range

Cell E1 contains a formula that returns the current date, this formula is volatile meaning it recalculates more often than non-volatile functions.

Formula in cell E1:

The TODAY function returns the Excel date (serial number) of the current date.

Function syntax: TODAY()

Cell F1 calculates the date two workdays earlier, this formula is also volatile since it references cell E1.

Formula in cell F1:

The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

Function syntax: WORKDAY(start_date, days, [holidays])

Table1[Holidays] is a reference to a column in an Excel defined Table.

I recommend adding holidays to this formula to prevent dates when the stock market is closed.

Cell G1 returns a date one week earlier than the date in cell E1.

Formula in cell G1:

The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

Function syntax: WORKDAY(start_date, days, [holidays])

I recommend adding holidays to this formula to prevent dates when the stock market is closed.

Cell H1 returns a date two weeks earlier than the date in cell E1.

Formula in cell H1:

The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

Function syntax: WORKDAY(start_date, days, [holidays])

I recommend adding holidays to this formula to prevent dates when the stock market is closed.

Cell I1 returns a date three weeks earlier than the date in cell E1.

Formula in cell I1:

The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

Function syntax: WORKDAY(start_date, days, [holidays])

I recommend adding holidays to this formula to prevent dates when the stock market is closed.

This formula calculates a date one month earlier than the date in cell E1 and makes sure the date is not a Saturday or Sunday. I recommend adding holidays to the WORKDAY function to prevent dates when the stock market is closed.

Formula in cell J1:

2.1 Explaining formula

Step 1 - Calculate the year based on an Excel date

The YEAR function converts a date to a number representing the year in the date.

Function syntax: YEAR(serial_number)

YEAR($E$1)

becomes

YEAR(44887)

and returns 2022.

Step 2 - Calculate the month

The MONTH function extracts the month as a number from an Excel date.

Function syntax: MONTH(serial_number)

MONTH($E$1)-1

The minus character lets you subtract numbers in an Excel formula. This allows us to calculate the month before.

MONTH($E$1)-1

becomes

MONTH(44887)-1

becomes

11-1

and returns 10.

Step 3 - Calculate the day

The DAY function extracts the day as a number from an Excel date.

Function syntax: DAY(serial_number)

DAY($E$1)

becomes

DAY(44887)

and returns 22.

Step 4 - Create an Excel date based on year, month, and day values

The DATE function returns a number that acts as a date in the Excel environment.

Function syntax: DATE(year, month, day)

DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1))

becomes

DATE(2022, 10, 22)

and returns

44856.

Step 5 - Calculate weekday number

The WEEKDAY function converts a date to a weekday number from 1 to 7.

Function syntax: WEEKDAY(serial_number,[return_type])

WEEKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),2)

becomes

WEEKDAY(44856,2)

and returns 6.

Step 6 - Identify Saturdays and Sundays

The larger than and smaller than characters lets you compare number to number, the result is a boolean value TRUE or FALSE.

A value larger than 5 tells us that the date is a Saturday or Sunday.

WEEKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),2)>5

becomes

6>5

and returns TRUE.

Step 7 - Check if the date is a holiday

The equal sign lets you check if a value is equal to another value, and the result is a boolean value TRUE or FALSE.

DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1))=Table1[Holidays]

becomes

44856={44746; 44525; 44889}

and returns

{FALSE; FALSE; FALSE}

10/22/2022 is not in the list of holidays.

Step 8 - Check if at least one of the boolean values is equal to TRUE

The OR function evaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.

Function syntax: OR(logical1, [logical2])

OR(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1))=Table1[Holidays]))

becomes

OR({FALSE; FALSE; FALSE})

and returns

FALSE.

Step 9 - Check if at least one of the boolean values is equal to TRUE

The OR function evaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.

Function syntax: OR(logical1, [logical2])

OR(WEEKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),2)>5,OR(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1))=Table1[Holidays]))

becomes

OR(TRUE, FALSE)

and returns

TRUE.

Step 10 - Calculate the workday date one day back

The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

Function syntax: WORKDAY(start_date, days, [holidays])

WORKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),-1,,Table1[Holidays])

becomes

WORKDAY(44856,-1)

and returns

44854.

Step 11 - Check if the date is a weekend or a holiday

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

This IF function checks if the date is a weekend or a holiday specified in Table1[Holidays]. If true then return the first earlier workday, if not true then show the date.

IF(OR(WEEKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),2)>5,OR(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1))=Table1[Holidays])),WORKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),-1,Table1[Holidays]),DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)))

becomes

IF(TRUE, 44854, 44856)

and returns

44854

Step 12 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

IF(OR(WEEKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),2)>5,OR(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1))=Table1[Holidays])),WORKDAY(DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)),-1,Table1[Holidays]),DATE(YEAR($E$1),MONTH($E$1)-1,DAY($E$1)))

y - $E$1

x - DATE(YEAR(y),MONTH(y)-1,DAY(y))

LET(y,$E$1,x,DATE(YEAR(y),MONTH(y)-1,DAY(y)),IF(OR(WEEKDAY(x,2)>5,OR(x=Table1[Holidays])),WORKDAY(x,-1,,Table1[Holidays]),x))

Back to top

This formula is the same as in cell J1 with one minor exception, it subtracts by two instead of one.

Formula in cell K1:

This formula is the same as in cell J1 with one minor exception, it subtracts by three instead of one.

Formula in cell L1:

This formula is the same as in cell J1 with one minor exception, it subtracts by four instead of one.

Formula in cell M1:

This formula is the same as in cell J1 with one minor exception, it subtracts by five instead of one.

Formula in cell N1:

This formula is the same as in cell J1 with one minor exception, it subtracts by six instead of one.

Formula in cell O1:

This formula is the same as in cell J1 with one minor exception, it subtracts by nine instead of one.

Formula in cell P1:

This formula is the same as in cell J1 with one minor exception, it subtracts by twelve instead of one.

Formula in cell Q1:

Back to top

3. Get historical stock prices based on ticker/symbol and date range

Formula in cell F3:

3.1 Explaining formula

Step 1 - Get the closing stock price value for a given date

The STOCKHISTORY function downloads stock prices based on a stock quote

Function syntax: STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

STOCKHISTORY($B3,F$1,F$1,0,0,1)

Cell reference $B3 is "locked" to column B and cell references F$1 are locked to row 1. This will change the cell references appropriately as we copy the cell and paste it to adjacent cells.

STOCKHISTORY($B3,F$1,F$1,0,0,1)

becomes

STOCKHISTORY("SKYY", 44887, 44887,0,0,1)

and returns

60.33

Step 2 - Divide latest stock price value by historical stock price value

The division character lets you divide numbers in an Excel formula.

$D3/STOCKHISTORY($B3,F$1,F$1,0,0,1)

becomes

61.63/60.33

and returns

1.02154815183159

Step 3 - Subtract the result by 1

The minus character lets you subtract numbers in an Excel formula. This allows us to calculate the percentage even if the stock price is declining.

$D3/STOCKHISTORY($B3,F$1,F$1,0,0,1)-1

becomes

1.02154815183159 - 1

and returns

0.021548151831593

Back to top

3.2 How to format a cell showing a percentage

1. Select cell F3
2. Press CTRL + 1 to open the "Format Cells" dialog box.
3. Press with left mouse button on "Percentage".
4. Press with left mouse button on "OK" button to apply changes.

Back to top

3.3 Copy cell and paste to adjacent cells

1. Select cell F3.
2. Press CTRL + c to copy the cell.
3. Select cell range F3:Q37.
4. Press CTRL + v to paste the formula.

Back to top

4. Enable Autofilter

1. Select any cell in the table.
2. Go to tab "Data" on the ribbon.
   
3. Press with left mouse button on the "Filter" button.

Buttons next to header names appear, they let you sort the table.

Back to top

5. Apply conditional formatting

1. Select cell range E3:E37.
2. Go to tab "Home" on the ribbon.
   
3. Press with left mouse button on the "Conditional Formatting" button.
   
4. Press with left mouse button on "Color Scales".
5. Press with left mouse button on the "Green - Yellow - Red Color Scale"
   
6. Copy cell range E3:E37.
7. Select cell range F3:F37.
8. Press with right mouse button on on the selected cell range.
   
9. Select "Paste Special...", a dialog box appears.
   
10. Press with left mouse button on "Formats".
11. Press with left mouse button on "OK" button.
    
12. Repeat above steps with the remaining columns.
    

Back to top

Get Excel * .xlsx file

Back to top

 

Finance category

More than 1300 Excel formulas

Excel categories

Leave a Comment