Google Sheets has the function GOOGLEFINANCE which returns various financial numbers with live updates. It's really useful. But, it's got a serious bug: it doesn't always return results in the correct currency. I know "marketcap" is buggy and I suspect other attributes are similarly broken.
Here are several values as of the evening of January 21st, 2022. They're mostly correct, but Sony's market cap is very wrong:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ticker Symbol: | MSFT | AAPL | SONY | Notes | |
| 2 | attribute | |||||
| 3 | currency | USD | USD | USD | ||
| 4 | shares | 7,507,980,000 | 16,334,370,000 | 1,261,059,000 | ||
| 5 | price | $296.03 | $162.41 | $111.63 | ||
| 6 | marketcap | $2,222,587,310,234.00 | $2,652,863,467,418.00 | $16,337,304,355,000.00 | Sony's value is very wrong | |
| 7 | ||||||
| 8 | calculated | |||||
| 9 | price×shares | $2,222,587,319,400.00 | $2,652,865,031,700.00 | $140,772,016,170.00 | This should be approximately equal to marketcat | |
| 10 | ||||||
| 11 | January 21, 2022 values from Google Search | https://www.google.com/search?q=TICKER+stock | ||||
| 12 | price | $296.03 | $162.41 | $111.63 | ||
| 13 | marketcap | $2,220,000,000,000.00 | $2,650,000,000,000.00 | $144,890,000,000.00 | Google Search only provides a few digits of precision | |
Here are the formulas generating these results:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ticker Symbol: | MSFT | AAPL | SONY | Notes | |
| 2 | attribute | |||||
| 3 | currency | =GOOGLEFINANCE(C$1, $B3) | =GOOGLEFINANCE(D$1, $B3) | =GOOGLEFINANCE(E$1, $B3) | ||
| 4 | shares | =GOOGLEFINANCE(C$1, $B4) | =GOOGLEFINANCE(D$1, $B4) | =GOOGLEFINANCE(E$1, $B4) | ||
| 5 | price | =GOOGLEFINANCE(C$1, $B5) | =GOOGLEFINANCE(D$1, $B5) | =GOOGLEFINANCE(E$1, $B5) | ||
| 6 | marketcap | =GOOGLEFINANCE(C$1, $B6) | =GOOGLEFINANCE(D$1, $B6) | =GOOGLEFINANCE(E$1, $B6) | ||
| 7 | ||||||
| 8 | calculated | |||||
| 9 | price×shares | =C4*C5 | =D4*D5 | =E4*E5 | This should be approximately equal to marketcat | |
| 10 | ||||||
| 11 | January 21, 2022 values from Google Search | https://www.google.com/search?q=TICKER+stock | ||||
| 12 | price | $296.03 | $162.41 | $111.63 | ||
| 13 | marketcap | =2.22*1000000000000 | =2.65*1000000000000 | =144.89*1000000000 | Google Search only provides a few digits of precision | |
But if we assume the 16,337,304,355,000 is actually Japanese yen and convert to US dollars, we get $143,694,139,636.84, which is just about what we'd expect.
You can see the spreadsheet I did these calculations in. The "Live Updates" tab is using GOOGLEFINANCE, and so will almost certainly be different from the above. The "As of January 21, 2022" tab was created by copying the data from "Live Updates" and using Edit > Paste special > Values only, essentially "freezing" the values to what I'm seeing as I write this.
Hopefully Google will eventually fix this bug; I suspect this isn't helping anyone. If the need to preserve the behavior for backward compatibility, they could add "marketcap2" with the correct value, or add "marketcapcurrency" which would return the currency identifier, allowing easy conversion (probably through =GOOGLEFINANCE("CURRENCY:JPYUSD")). In the meanwhile, be aware of it, double check your results, and avoid the "marketcap" attribute.