Stop rounding when importing Google finance currency

Question

I am importing currency from Google finance to Google spreadsheets (latest version). The number that is imported is rounded both in terms of what is displayed and the underlying number. Is there a way to get the actual underlying currency number to all necessary decimal places?

Answer 1

Look at your menu bar and select:

Format > Number > More Formats > Custom Number Format

Answer 2

I used the external lookup - =ImportHTML("http://www.xe.com","table", 1)

in a unused part of the spreadsheet and then sourced the appropriate rate from the table contents. Will probably work with other currencies too - look at xe.com Updates every minute which is fine for my purposes.

Answer 3

=round((googlefinance("asx:"&B12,"marketcap")/googlefinance("asx:"&B12,"shares")),4)

Where:

• ASX (Australian Stock eXchange) can be replaced for TSE, etc or removed for NASDAQ
• B12 is the Ticker.