• 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

Stock Data Types searches are Unwieldy

Excel Version
  1. 365
Trying to find the exact instrument you care about is an exasperating experience. You type in what you think it is, select Data Types | Stocks and it either reports something wrong or finds nothing. Then on the Data Selector pane, you type in all permutations you can think of and still comes up craps!

Just try to find certain indexes or commodities and the annoyance will become apparent. I managed to get some of them, but failed utterly for others.

Here in OneDrive I put a file that contains these commodities and indexes. Download it or even use it in Excel on the Web. That way you can just copy the symbols I have discovered without having to type them in and then gamble with the results.

It's there because copying the sheet below here in the Forum and then pasting into Excel does NOT enable the Data Types. When new data becomes available, I will be sure to update the file. I encourage anyone who has found the tough symbols to let me know and I will update this file.

For stocks, it helps to type in the exchange and then the symbol. For example a TSX stock is easily found by typing this: XTSE:CNQ That will get you Canadian Natural Resources on the TSX. Just typing CNQ will get you CNQ on the foreign exchange, the NYSE, where it is interlisted. The exchange codes are all listed in the MS help article.

Some of these instruments don't pop up when you type in what I have suggested in the Table. You will have to open up the Data Selector and then scroll through the list, or you will have to erase what is in the Search box inside the Data Selector, type in my suggestion and then scroll through the list.

I hope I've helped at least one frustrated person.

StockDataTypes_MrExcelForum.xlsx
CDEFGHI
3Security SoughtType this ExactlyEdit needed in Data Selector?Resulting Stock Data TypeResulting TickerPriceLast trade time
4Toronto Stock Exchange 300 Composite IndexTSXTSX-Toronto Stock Exchange 300 Composite IndexTSX$ 19,624.74 
5S&P/TSX Venture CompositeJXS&P/TSX Venture CompositeJX$ 610.32 
6DJIADJIDOW JONES INDU AVERAGE NDXDJI$ 32,432.08 
7NASDAQ Composite IndexNASDAQNASDAQ Composite IndexCOMP$ 11,768.84 
8NASDAQ 100 INDEXNASDAQ 100 INDEXNASDAQ 100 IndexNDX$ 12,673.07 
9S&P 500 INDEXINXYesS&P 500 INDEXINX$ 3,977.53 
10CBOE MKT VOLATILITY IDXCBOE MKT VOLATILITY IDXCBOE MKT VOLATILITY IDXVIX$ 20.60 
11DEUTSCHE BORSE DAX INDEXDEUTSCHE BORSE DAX INDEXDEUTSCHE BORSE DAX INDEXDAX€ 15,127.68 
12NIKKEI 225 INDEXN225NIKKEI 225 INDEXN225¥ 27,476.87 
13Cdn Dollar - US DollarCADUSDCAD/USDCADUSD$ 0.733/27/2023 21:41
14Cdn Dollar - EuroCADEURCAD/EURCADEUR€ 0.683/27/2023 21:41
15Isreal Shekel - Saudi RiyalSARILSSAR/ILSSARILS₪ 0.953/27/2023 21:41
16WTI FuturesUSOILLight Sweet Crude Oil@CL0Y$ 72.893/27/2023 20:59
17Brent FuturesUKOILBrent Crude@BZ0Y$ 78.083/27/2023 20:59
18Gasoline FuturesUS GASOLINEGasoline@RB0Y$ 2.653/27/2023 20:59
19Heating Oil futuresUS HEATING OILHeating Oil@HO0Y$ 2.643/27/2023 20:59
20Copper FutresUS COPPERYesCopper@HG0Y$ 4.103/27/2023 20:59
21Gold FuturesUS GOLDYesGold@GC0Y$ 1,957.703/27/2023 20:59
22Silver FuturesUS SILVERSilver@SI0Y$ 23.223/27/2023 20:59
23US Treasury 2 yearUS2YUS Treasury 2YUS2Y$ 101.133/27/2023 21:05
24US Treasury 5 yearUS5YUS Treasury 5YUS5Y$ 101.793/27/2023 21:05
25US Treasury 10 yearUS10YUS Treasury 10YUS10Y$ 99.673/27/2023 21:05
26US Treasury 30 yearUS30YUS Treasury 30YUS30Y$ 97.443/27/2023 21:05
2710 Y TSY YLD NDX10 Y TSY YLD NDX10 Y TSY YLD NDXTNX$ 35.28 
28TSX 60  
29TSX Financials  
30TSX Energy  
31FTSE 100  
32Russel 2000  
33CAC 40  
34STOXX 600  
35Hang Seng  
Sheet1
Cell Formulas
RangeFormula
H4:H35H4=IFERROR([@[Resulting Stock Data Type]].Price,"")
I4:I35I4=IFERROR([@[Resulting Stock Data Type]].[Last trade time],"")
G4:G27G4=F4.[Ticker symbol]
Author
DRSteele
Views
3,250
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

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
Back
Top