VBA for Stock Data Type

Question

excel.range.converttolinkeddatatype

Below code converts stock symbol to company, just like clicking screenshot menu bar Stock command.

Issue: some symbols are from multiple exchanges, take HUT for example, if I run below code, the return value for HUT will be Hurricane Energy PLC, but that company is not what I want, I want the return value to be Hut 8 Mining Corp. If I do it manually, it gives me an option to pick the company. But below code does not give me the option.

How to fix it? Make it to match US stock symbols only. Or US stock exchanges only.

It seems that: by default, the system chooses first one from Data Selector, is it possible to set US stock exchanges at the beginning? So that it will pick the symbol from US stock exchange.

Thanks.

Range(ShtStockQuote.Cells(2, 14), ShtStockQuote.Cells(LastRow, 14)).ConvertToLinkedDataType ServiceID:=268435456, LanguageCulture:="en-US"  

Answer 1

Hello hello!

I ran across this topic as I had a similar challenge to deal with and while I do not have a complete answer to you question I might have a satisfactory one.

Basically every stock is traded on a specific market, or in other words a stock is found on a market.
However, certain stock symbols might be traded on more than one market. Your example falls into that category, being on both Nasdaq US and Toronto Stock Exchange. If this is a case the data selector will give you the two options, so that you can manually select.

However, If you are converting the data from the code it will not select it for you from the cell value due to the above mentioned. Instead it converts the data with a question mark so that you "go" and check it up.

Thus you also have to specify the market symbol. In other words, instead of just HUT (which is a bit ambiguous), writing XNAS:HUT will "tell" the ConvertToLinkedDataType method the specific thing you want. Kind of like telling it "from this market fetch me this stock". In this case XNAS represends The Nasdaq Stock Market. If you, would like the one from the Toronto Stock Exchange then you would specify XTSE:HUT in the cell value.

"How to fix it? Make it to match US stock symbols only. Or US stock exchanges only."

To answer this question, in the cell I would add XNAS:HUT then call the method ConvertToLinkedDataType on that value.
If I would like to match with stocks only listed in the US then I would think what the markets in the US are. The main ones of course will be The New York Stock Exchange (XNYS) and Nasdaq (XNAS).

"is it possible to set US stock exchanges at the beginning?"
While I think I explained quite in detail in the above texts what's going on, what I would do is make a mapping between Markets and Stocks. If I would have a list of stock symbols, I would be going through it, I would then check every symbol in a cell.value and match it to its respective market, replacing then the value with markgetSymbol:stockSymbol, and then calling the ConvertToLinkedDataType method.

Hope this answers your question and if not I think i can provide maybe a better answer.

Cheers,
Anthony