• If you would like to post, please check out the MrExcel Message Board FAQ and register here.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

North American Stock Market Indexes with 52-Week Performance Chart

Excel Version
  1. 365
With the intention to help those struggling to find stock names and symbols for Data Types | Stocks, I put in some effort. I have uploaded to OneDrive a file containing all the stocks in the TSX Composite, the Dow Jones Industrial Average, the S&P500 and the NASDAQ100, current as of December 29, 2020. Refresh All will update the entire file, and the Tables can be sorted any way desired. Included are hyperlinks that open a web browser to either the TSX's website or CNBC's website. The hyperlinks can easily be adjusted for your own personal needs, of course. Notice there is conditional formatting throughout.

Also included is a performance chart, or mini-graph, for the 52-week data. Since there is no Sparkline yet that has this functionality and I was not about to create a thousand tiny Excel Charts throughout the file, I used some formulas to mimic one. The formula in L9 uses the REPT and CELL functions along with some UNICHAR characters I chose; it will adjust itself for the column width, but I found it is best to have an even number column width (such as 24), to have the font at the same size as the default, and the worksheet Zoom at 100%

Sep 18, 2020: Excel Insiders can read this article about how to use it in their portfolio tracker template. It also shows that Microsoft really is a good listener.

I believe this will need to be opened up in the Desktop app because some of the formulas and Data Types won't function in the Online version.

Here is an extract of the mini-chart so that you are not required to get the OneDrive file:

Book1
ABCDEFGHIJKLMN
8NAMESYMBOLExchangePriceChangeChange (%)Last trade timeIndustryHyperlink52 week high52 week low52 Week ChartTSX CompTSX 60
9Brookfield Asset Management Inc. (XTSE:BAM.A)BAM.AXTSE $ 46.69 -$ 0.41 -0.87%7/16/2020 14:00Investment Banking & Investment Services BAM.A $ 60.48 $ 31.35 ⎼⎼⎼⎼⎼⎼⎼◊⎼⎼⎼⎼⎼⎼⎼ Composite TSX 60
Sheet1
Cell Formulas
RangeFormula
B9B9=A9.[Ticker symbol]
C9C9=A9.[Exchange abbreviation]
D9D9=A9.Price
E9E9=A9.Change
F9F9=A9.[Change (%)]
G9G9=A9.[Last trade time]-6/24
H9H9=IFERROR(A9.Industry,"")
I9I9=HYPERLINK($L$6&B9,B9)
J9J9=A9.[52 week high]
K9K9=A9.[52 week low]
L9L9=REPT(UNICHAR(9148),MAX(CELL("width",L9)*(D9-K9)/(J9-K9)+IF((D9-K9)/(J9-K9)=1,-1))) &UNICHAR(9674) &REPT(UNICHAR(9148),MAX(CELL("width",L9)*(1-(D9-K9)/(J9-K9))+IF((D9-K9)/(J9-K9)=0,-1)))
  • Like
Reactions: Dreamteam
Author
DRSteele
Views
795
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top