Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
How to Speedly Load SymbolNames in the srcSymbol Tab Using EXCEL 365 Stock Function
Folks,
Here is a speedy way to load SymbolNames in the srcSymbol tab using the EXCEL 365 Stocks Macro.
Step 1. Enter the stock ticker and exchange identifier (e.g. .TO)in a blank cell in Column A
Step 2. Enter the stock ticker in Column B (no exchange identifier e.g. .TO) in the same row as Column A above.
Step 4. Highlight the cell in Column B you copied the stock ticker into.
Step 4. Goto EXCEL's Data Ribbon
Step 5. Click on Stocks
Step 6. A pop-up window may show on the right-hand side of your screen giving the exchanges that have your symbol. If not Microsoft defaults to US exchanges. For Canadian stocks and ETFs. Enter the stock ticker and then .CA e.g. RY.CA then you will get the Royal Bank on the TSE, not the NYSE.
A special trick, if you do this for the first security in Column B and then insert rows in the table, EXCEL will remember the formating. So when you enter the new stock ticker in Column B, the Stocks macro is already live.
Warning: This method does not work for index tickers.
Hope this saves you some keypunching time.
Here is a speedy way to load SymbolNames in the srcSymbol tab using the EXCEL 365 Stocks Macro.
Step 1. Enter the stock ticker and exchange identifier (e.g. .TO)in a blank cell in Column A
Step 2. Enter the stock ticker in Column B (no exchange identifier e.g. .TO) in the same row as Column A above.
Step 4. Highlight the cell in Column B you copied the stock ticker into.
Step 4. Goto EXCEL's Data Ribbon
Step 5. Click on Stocks
Step 6. A pop-up window may show on the right-hand side of your screen giving the exchanges that have your symbol. If not Microsoft defaults to US exchanges. For Canadian stocks and ETFs. Enter the stock ticker and then .CA e.g. RY.CA then you will get the Royal Bank on the TSE, not the NYSE.
A special trick, if you do this for the first security in Column B and then insert rows in the table, EXCEL will remember the formating. So when you enter the new stock ticker in Column B, the Stocks macro is already live.
Warning: This method does not work for index tickers.
Hope this saves you some keypunching time.
Comments
ie: I had some Canadian investments that I couldn't find a few months ago and now they show up accurately.
The =Stockhistory is also a somewhat need feature that I use for Currency History.
Regarding your "Warning about index tickers"
I have no issue with 365 pulling:
"DJI" "DOW JONES INDU AVERAGE NDX"
"TSX" "TSX-Toronto Stock Exchange 300 Composite Index"
However, Using the Excel 365 "Stocks" Function causes a issue when Scripts are run.
The Symbols.csv does not recognize "Symbolname" and returns "#VALUE" for "Symbolname" in the csv file.
From my attempts to directly use all the 365 "Stocks" feature have failed, my work around is:
I am currently running a Excel 365 workbook that somewhat replicates the "PortfolioSlicer-Source and converting each Worksheet to a csv file and saving in the Portfolio Data folder for each of the required csv files.
I use 365 "Stocks" feature to do Currency exchange and historical and current Symbol values for the "Quotes.csv".
I just overwrite the "Quotes.csv" as required.