Importing alternative cryptocurrency prices and non-US stock prices into Google Sheets
- Builtin GoogleFinance() function
- Alternative Cryptocurrencies
- Malaysian Stock Prices
- Indian Stock Prices
- Use ImportXML() for any data
Important: This is for Google Sheets, not Microsoft Excel.
Builtin GoogleFinance() function
For Bitcoin, currency conversions and US stocks , there is a builtin function you can use.
=GoogleFinance(“currency:BTCUSD”) to get Bitcoin price in US Dollars.=GoogleFinance(“USDMYR”) to convert US Dollars into Malaysian Ringgit.=GoogleFinance(“NYSE:DIS”) to get Disney stock price etc.
However, Google Finance functions don’t seem to support other crypto like Ripple or non-US stocks. Therefore, I had to find a different method to make it work.
The basic concept is we find a webpage that updates with the info we want and extract it from there.
“https://coinmarketcap.com/currencies/bitcoin/" is the web page where you are pulling the data from,
“//div[@class=’priceValue___11gHJ’]” is the specific class that holds the data in that page that you want.
For crypto, just use Coinmarketcap and search for crypto you want, change the “bitcoin” to the coin name that is in the URL.
For Ethereum (ETH)
=IMPORTXML(“https://coinmarketcap.com/currencies/ethereum/", “//div[@class=’priceValue___11gHJ’]”)For ZCash
For stock prices, same concept applies.
However, you just need to find a website that publishes live stock prices and figure out the URL (web address) pattern and the class name.
Malaysian Stock Prices
For Malaysia stock price, I used malaysiastock.biz.
The “securityCode=5079” part in the web address is how they differentiate the stock.
“//*[@id=’MainContent_lbQuoteLast’]” is the part in the web page where the price is listed.
So for a different stock, I just find the security code and replace.
=ImportXml(“https://www.malaysiastock.biz/Corporate-Infomation.aspx?securityCode=5347", “//*[@id=’MainContent_lbQuoteLast’]”)For CARLSBG
Indian Stock Prices
For Indian stock prices, use moneycontrol.com
=IMPORTXML("https://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI", "//div[@class='inprice1 nsecp']")
Here, the URL is not as simple above cases but same principle applies.
“https://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI” is the web page address we want to use.
“//div[@class=’inprice1 nsecp’]” is the part in the web page where the price is listed.
For J.K.Cement Ltd.
=IMPORTXML("https://www.moneycontrol.com/india/stockpricequote/cement-major/jkcement/JKC03", "//div[@class='inprice1 nsecp']")For Reliance
=IMPORTXML("https://www.moneycontrol.com/india/stockpricequote/refineries/relianceindustries/RI", "//div[@class='inprice1 nsecp']")
Use ImportXML() for any data
- Go to the web page that holds the data you want to import
- Replace A above with the web page address (URL).
- Select the data displayed in the web page and click Inspect (See image)
- Copy the class name or id name of the <div>.
- Replace C or D with id or class name of the div that you copied
- Replace the //*[@id=’C’] or //div[@class=’D’] into B.
Thank you for reading. Please leave a comment if you found this useful.