Is there easy way to identify cells with foreign currency?

I have thousands of symbol, and use VBA to write formula STOCKHISTORY for each symbol. Majority returns with US currency, except a few (e.g. AGI), not sure why.

https://finance.yahoo.com/quote/AGI/

It supposes to pull data for this company, but excel STOCKHISTORY does not work properly with all symbols. But it is okay, not a lot of them.

I would like to identify those symbols with non US currency(then remove them from my spreadsheet), how should I tell if a cell is not US currency? It is too annoying to review manually, and I could miss catching some symbols.

Originally Posted by 6StringJazzer

The currency is based on the exchange that the data is pulled from. You can force it to pull in USD by specifying the exchange, but you would have to know the exchange for each stock. For example this specifies the NYSE:

Formula:

Please Login or Register  to view this content.

(no need for "-0" as in your formula)

Exchange codes listed here: https://support.microsoft.com/en-us/...a-c5a6c8e787e6

I am unable to find a way to determine the currency type or cell format returned by STOCKHISTORY.

But there are some other US exchanges.

NYSE Arca ARCX
OTC Markets OTCM
Nasdaq Stock Market XNAS
New York Stock Exchange XNYS
NASDAQ Stockholm XSTO

Please Login or Register  to view this content.

Artik

I get the impression that you have not tried this code when the cell with formula is active.

Artik

Originally Posted by Artik

I get the impression that you have not tried this code when the cell with formula is active.

Artik

Days ago, I manually reviewed the sheet and removed some symbols (non US currency). My current symbol list comes up with US dollar.

But I really appreciate your code, I will save the code, in case I need it in the future.

@Artik

Last time I manually reviewed and removed non US currency symbols.

With symbol list updated periodically, there are new symbols with non US currency. I use your code to detect symbol with non US currency symbols, it does work.

One question: what does 2 mean in Left(ActiveCell.Text, 2) ? Do I need only one character from the left?