- Excel Version
- 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:
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 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 8 | NAME | SYMBOL | Exchange | Price | Change | Change (%) | Last trade time | Industry | Hyperlink | 52 week high | 52 week low | 52 Week Chart | TSX Comp | TSX 60 | ||
| 9 | Brookfield Asset Management Inc. (XTSE:BAM.A) | BAM.A | XTSE | $ 46.69 | -$ 0.41 | -0.87% | 7/16/2020 14:00 | Investment Banking & Investment Services | BAM.A | $ 60.48 | $ 31.35 | ⎼⎼⎼⎼⎼⎼⎼◊⎼⎼⎼⎼⎼⎼⎼ | Composite | TSX 60 | ||
Sheet1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B9 | B9 | =A9.[Ticker symbol] |
| C9 | C9 | =A9.[Exchange abbreviation] |
| D9 | D9 | =A9.Price |
| E9 | E9 | =A9.Change |
| F9 | F9 | =A9.[Change (%)] |
| G9 | G9 | =A9.[Last trade time]-6/24 |
| H9 | H9 | =IFERROR(A9.Industry,"") |
| I9 | I9 | =HYPERLINK($L$6&B9,B9) |
| J9 | J9 | =A9.[52 week high] |
| K9 | K9 | =A9.[52 week low] |
| L9 | L9 | =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))) |
