• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    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

In an effort 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 July 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 24) and to have the font at size 12.

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)))
Excel Version
365
  • Like
Reactions: Dreamteam
Author
DRSteele
Views
137
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

Some videos you may like

This Week's Hot Topics

Top