VBA for Stock Data Type

VAer-4038 771 Reputation points
2021-10-18T16:49:53.06+00:00

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"  

141440-stock-data-type.jpg

Office Visual Basic for Applications
Office Visual Basic for Applications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Visual Basic for Applications: An implementation of Visual Basic that is built into Microsoft products.
1,506 questions
{count} vote

1 answer

Sort by: Most helpful
  1. Anthony 6 Reputation points
    2022-03-04T15:55:57.877+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.