Coins: 14,745
Exchanges: 1,129
Market Cap: $2.556T 1.9%
24h Vol: $119.609B
Gas: 7 GWEI
Go Ad-free
API
TABLE OF CONTENTS

How to Pull Live Crypto & Stocks Prices into Excel

4.9
| by
Julia Ng
|
Edited by
Julia Ng
-

Having access to real-time price data is crucial for making informed decisions. Unsurprisingly, many financial analysts and traders use Microsoft Excel to stay on top of their portfolio, track stock and crypto performance, asset details, profit and loss, return on investment (ROI), among other metrics. In order to fetch live price data conveniently, we’ll be utilizing Excel’s native Stocks integration feature and CoinGecko’s crypto API.

This step-by-step guide will walk through:

  1. How to import real-time stocks data into Excel
  2. How to import live crypto data into Excel (including coins in trending categories)
  3. Bonus: Setting up a P&L tracker for your investment portfolio

Let’s jump in!

Disclaimer: This guide is prepared for Microsoft Excel users operating on Windows, and Microsoft’s Stocks data is an Office 365 subscription-only feature. Google Sheets users can alternatively refer to this guide on importing crypto data into Google Sheets.


Fetching Real-time Stocks Data in Excel

First, create a new spreadsheet on Microsoft Excel, labeling it ‘Stocks’. On cell A1, make a list of stocks you’re currently holding or that’s on your watchlist. This is the tab where you’ll import all stocks-related data using Excel’s native Data integration.

Once you’re done, select ‘Data’ in the top navigation bar. Highlight the row of stock tickers, and click on ‘Stocks’, which will activate the integration with Microsoft Bing’s database.

Fetch live stocks data in Microsoft Excel - Excel portfolio tracker

Next, click on the top right icon (‘Insert Data’), and a drop-down menu will appear:

Microsoft Excel Live Stock Prices

Select all fields you’d like information on - for each selection, a new column will appear and populate accordingly. In this guide, we’ve kept it simple and included just four columns: Ticker, Price, Change, Price Change % and Market Cap.

MS Excel spreadsheet stock ticker asset portfolio


How to Import Live Crypto Prices into Excel Spreadsheets

The easiest way to import live crypto prices is by using Excel's "Data from Web" feature, and connecting it with CoinGecko's crypto API endpoint /coins/markets. Head over to our comprehensive API documentation to retrieve this endpoint's Request URL.

CoinGecko Crypto API - Coins Markets price data endpoint

Coins List with Market Data is one of the most commonly used crypto API endpoints that traders, developers, projects and analysts rely on to fetch real-time crypto price data. With only two API calls on this endpoint, you can retrieve data of the top 500 cryptocurrencies listed on CoinGecko, which is typically enough for most. To import data for coins beyond the top 500 or smaller market cap cryptocurrencies, simply adjust the ‘Page’ parameters accordingly (each page displays 250 coins data).

Provide your API key and fill in the parameters:

vs_currency cryptocurrency api endpoint - CoinGecko API

Tip: If you’d like to import coins data for specific crypto categories rather than pulling in a whole list of top 250 or 500 cryptocurrencies, input the respective category id in the ‘category’ parameter field.

In the example below, we’ll look at pulling data for the ‘telegram-bots’ category.

Telegram bots crypto category price data

Click on the ‘Execute’ button to generate the Request URL.

In our example, the Request URL for the top 250 cryptocurrencies is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3

The Request URL for the next 250 cryptocurrencies (top 251 to 500) is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3

As highlighted in yellow above, the key difference between the two Request URLs generated is the page number.

Finally, the Request URL for the Telegram Bots category is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=telegram-bots&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3
 
Let’s head back to our Excel workbook and navigate to ‘Data’. This time, select ‘From Web’.

Excel Data From Web feature with tool tip

If ‘From Web’ is not available in your navigation bar, you can find it under Data > From Other Sources > From Web. If you’re a Mac OS user on Excel, you can instead rely on CryptoSheets, as this feature is applicable for Windows OS users only.

MS Excel - Get Data from Web screenshot guide

Upon clicking ‘From Web’, a pop-up window appears. Ensure the toggle is set to ‘Basic’, copy and paste the first Request URL into the input field and select ‘OK’. 

Enter API request URL into Excel Data from Web | CoinGecko

It may take a while to establish a connection to API servers on the Demo API plan.

Pop-up in Microsoft Excel when connecting Data from Web | CoinGecko

Paid API users may tweak the Request URL to call from the root URL ‘https://pro-api.coingecko.com/api/v3/coins/markets’ and append in their Pro API key at the end of the URL. The URL structure will appear as:

https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3?&x_cg_pro_api_key=YOUR_API_KEY
 
Once it’s loaded, Excel’s Power Query Editor appears in a new window. To avoid random query strings, you may choose to rename the query - in this example, we’ve renamed it to ‘coins/markets1-250’.
 
Microsoft Excel Power Query Editor | CoinGecko

Click on ‘To Table’ at the top right corner and select ‘OK’. You won’t need to select or enter a delimiter.

Microsoft Excel Power Query Editor Record to Table | CoinGecko

Select the expand icon beside Column1 and a drop-down menu will appear. As a default, all columns will be selected. You may also uncheck the box below that states ‘Use original column name as prefix’ so that columns will be cleanly labeled.

Microsoft Excel Power Query Editor Expand Record to Table | CoinGecko

Select ‘OK’ and the data will be expanded into various columns. Next, click on ‘Close & Load’ and the data will populate into a new worksheet, which we’ll label as ‘Top 500 Crypto + Categories’ (we’ll be adding in more cryptocurrencies in subsequent steps).

MS Excel power query editor Close & Load tooltip | CoinGecko

Repeat this process, but this time using the second Request URL with the ‘Page’ parameter set to ‘2’. Instead of closing and loading this query immediately, we’ll import specific crypto categories data in the same manner as well. Given their recent rally, we’ve imported cryptocurrencies from Telegram Bots, Real World Assets and Animal Racing categories.

Now toggle back to the first query ‘/coins/markets1-250’ in the Power Query Editor and select ‘Append Queries’.

Append Queries

A dialogue box appears and you can toggle to ‘Three or more tables’ and select the tables to append accordingly. This essentially combines the top 500 crypto data with specific categories (Telegram Bots, Real World Assets and Animal Racing) from the /coins/markets/ API endpoint. Click on ‘OK’ and ‘Close & Load’.

Microsoft Excel Dialogue Box Append Queries in Power Editor | CoinGecko

In just a few clicks and under 5 minutes, you now have a real-time, functional Excel workbook importing stocks and crypto price data! Specifically for crypto, you have a consolidated list of top 500 cryptocurrencies data and coins from trending categories you’re watching.

Top 500 cryptocurrencies & Coin Categories data in Excel | CoinGecko

Refreshing the Data

On Excel, you can toggle your preferred refresh frequency by navigating to Data > Refresh All > Connection Properties. Deselect ‘Enable background refresh’ and ‘Refresh every 30 minutes’ if you’d like to manually refresh and conserve the number of API calls made, or opt for a 10-min auto-refresh like in the screenshot below.

Refresh the Data Connection Settings

Finally, we’ll move on to how you can customize your stocks and crypto portfolio dashboard.

Enjoyed this guide? Be sure to check out this master tutorial covering various endpoints on fetching crypto prices into Excel.


How to Create a Crypto Tracker on Excel Spreadsheets

With real-time stocks and crypto price data that's pulled into your Excel spreadsheet, you can easily create an Excel crypto tracker that automates your profit and loss (PNL) using the VLOOKUP formula, and customize it to your needs. Additionally, performance metrics will automatically refresh based on your refresh control settings.

The example below shows a simple and straightforward tracker using the VLOOKUP formula, to extract data from the raw databases Stocks and Top 500 + Categories.

VLOOKUP stocks and crypto data in excel to build combined view of investment portfolio

Tip: Use the =IMAGE(“url”) formula to bring the crypto coins images to life!

Add formulas in the gray columns accordingly:

  • Holdings Value – Multiply Holdings # by Current Price. This depicts how much value your stocks and crypto holdings are worth, in fiat currency.
  • P&L – The difference between Holdings Value and Total Invested, essentially your unrealized profit or loss, if the position was closed at that time.
  • ROI % – Divide P&L by Total Invested to derive your Return on Investment, which determines how profitable your investment is. The higher your ROI, the more profitable your investment.

Finally, set up chart visualizations on your Excel crypto tracker by selecting the relevant cells and navigating to Insert > Recommended Charts. You’re set!

Excel portfolio tracker - Live Asset Investment Portfolio for Stocks & Crypto on MS Excel Spreadsheets


Advanced traders who want access to more API endpoints, historical prices and avoid getting rate limited, may consider subscribing to our Analyst API plan. The CoinGecko API currently has 40+ endpoints, tracks 10,000+ coins across 700+ exchanges and 3,000+ NFT collections serving billions of API calls each month.

Advanced traders might also find these useful API endpoints particularly useful:

  • /coins/top_gainers_losers - get the top 30 coins with the largest price gains and losses based on specific time frames
  • /global/market_cap_chart - get historical global market cap and volume data, by no. of days away from now
  • /nfts/markets – track NFT floor prices, market cap and volume

CoinGecko API - Unlock Pro Features


Keen to learn more and maximize your crypto data usage? Check out our full list of API resources – we've published guides on developing crypto trading strategies, backtesting and more!

CoinGecko's Content Editorial Guidelines
CoinGecko’s content aims to demystify the crypto industry. While certain posts you see may be sponsored, we strive to uphold the highest standards of editorial quality and integrity, and do not publish any content that has not been vetted by our editors.
Learn more
Want to be the first to know about upcoming airdrops?
Subscribe to the CoinGecko Daily Newsletter!
Join 600,000+ crypto enthusiasts, traders, and degens in getting the latest crypto news, articles, videos, and reports by subscribing to our FREE newsletter.
Tell us how much you like this article!
Vote count: 16
Julia Ng
Julia Ng
Julia leads Growth at CoinGecko and is passionate about onboarding more women onto Web3. That said, she is generally poor at timing the market, so she DCAs for safety. Follow the author on Twitter @ngxinyajulia

More Articles

Select Currency
Suggested Currencies
USD
US Dollar
IDR
Indonesian Rupiah
TWD
New Taiwan Dollar
EUR
Euro
KRW
South Korean Won
JPY
Japanese Yen
RUB
Russian Ruble
CNY
Chinese Yuan
Fiat Currencies
AED
United Arab Emirates Dirham
ARS
Argentine Peso
AUD
Australian Dollar
BDT
Bangladeshi Taka
BHD
Bahraini Dinar
BMD
Bermudian Dollar
BRL
Brazil Real
CAD
Canadian Dollar
CHF
Swiss Franc
CLP
Chilean Peso
CZK
Czech Koruna
DKK
Danish Krone
GBP
British Pound Sterling
GEL
Georgian Lari
HKD
Hong Kong Dollar
HUF
Hungarian Forint
ILS
Israeli New Shekel
INR
Indian Rupee
KWD
Kuwaiti Dinar
LKR
Sri Lankan Rupee
MMK
Burmese Kyat
MXN
Mexican Peso
MYR
Malaysian Ringgit
NGN
Nigerian Naira
NOK
Norwegian Krone
NZD
New Zealand Dollar
PHP
Philippine Peso
PKR
Pakistani Rupee
PLN
Polish Zloty
SAR
Saudi Riyal
SEK
Swedish Krona
SGD
Singapore Dollar
THB
Thai Baht
TRY
Turkish Lira
UAH
Ukrainian hryvnia
VEF
Venezuelan bolívar fuerte
VND
Vietnamese đồng
ZAR
South African Rand
XDR
IMF Special Drawing Rights
Cryptocurrencies
BTC
Bitcoin
ETH
Ether
LTC
Litecoin
BCH
Bitcoin Cash
BNB
Binance Coin
EOS
EOS
XRP
XRP
XLM
Lumens
LINK
Chainlink
DOT
Polkadot
YFI
Yearn.finance
Bitcoin Units
BITS
Bits
SATS
Satoshi
Commodities
XAG
Silver - Troy Ounce
XAU
Gold - Troy Ounce
Select Language
Popular Languages
EN
English
RU
Русский
DE
Deutsch
PL
język polski
ES
Español
VI
Tiếng việt
FR
Français
PT
Português
All Languages
AR
العربية
BG
български
CS
čeština
DA
dansk
EL
Ελληνικά
FI
suomen kieli
HE
עִבְרִית
HI
हिंदी
HR
hrvatski
HU
Magyar nyelv
ID
Bahasa Indonesia
IT
Italiano
JA
日本語
KO
한국어
LT
lietuvių kalba
NL
Nederlands
NO
norsk
RO
Limba română
SK
slovenský jazyk
SL
slovenski jezik
SV
Svenska
TH
ภาษาไทย
TR
Türkçe
UK
украї́нська мо́ва
ZH
简体中文
ZH-TW
繁體中文
Login to track your favorite coin easily 🚀
By continuing, you agree to CoinGecko Terms of Service and acknowledge you’ve read our Privacy Policy
or
Forgot your password?
Didn't receive confirmation instructions?
Resend confirmation instructions
IT'S FREE! Track your favorite coin easily with CoinGecko 🚀
By continuing, you agree to CoinGecko Terms of Service and acknowledge you’ve read our Privacy Policy
or
Password must contain at least 8 characters including 1 uppercase letter, 1 lowercase letter, 1 number, and 1 special character
Didn't receive confirmation instructions?
Resend confirmation instructions
Forgot your password?
You will receive an email with instructions on how to reset your password in a few minutes.
Resend confirmation instructions
You will receive an email with instructions for how to confirm your email address in a few minutes.
Get the CoinGecko app.
Scan this QR code to download the app now App QR Code Or check it out in the app stores
coingecko
Continue in app
Track prices in real-time
Open App