How to Pull Live Crypto & Stocks Prices into Excel

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 using native features

2. How to import live crypto data into Excel using the official CoinGecko Add-in (including coins in trending categories)

3. Advanced: Using Power Query to merge multiple API endpoints

4. Bonus: Setting up a P&L tracker for your combined 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.

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

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.

---

How to Pull Live Crypto Prices into Excel Spreadsheets

You can pull and import live crypto prices into Excel spreadsheets using the official CoinGecko Excel Add-in and the =CG.PRICE() formula. This bypasses complex API setups and allows you to pull real-time data for thousands of coins directly alongside your stock data.

While Excel has a native feature for stocks, tracking crypto requires an external data source. The CoinGecko Add-in is the recommended solution because it allows you to fetch prices, market caps, and categorical data across the widest coverage of crypto assets using only simple spreadsheet formulas.

Setting Up the CoinGecko Excel Add-in

First, open Excel, go to Home > Add-ins, search for "CoinGecko", and install the official add-in.

Once installed, open the CoinGecko taskpane from your Excel ribbon. Enter your CoinGecko API key (a free Demo API key works perfectly) and click Save Settings.

Fetching Individual Crypto Prices

To fetch the current price of a specific asset to match your stock list, simply type this formula into your target cell:

=CG.PRICE("bitcoin")

Note: The formula uses CoinGecko's unique coin IDs rather than ticker symbols to ensure accuracy. You can find the full list of CoinGecko Coin IDs here. The coin ID is also displayed on every coin page under "API ID" in the Info section.

How to Bulk Import Crypto Market Data

To bulk import cryptocurrency market data into Excel, use the =CG.TOP() add-in formula. Typing =CG.TOP(1000) instantly generates a comprehensive table of the top 1,000 cryptocurrencies, eliminating the need to use Power Query to merge multiple API pages.

If you are building a large tracker and need to see the entire market at a glance, =CG.TOP() is the most efficient method.

Note: Ensure you have enough empty cells below and to the right so the array can expand without returning a #SPILL! error.

How to Pull Price Data for Crypto Categories

You can pull price data for specific crypto categories by using the =CG.TOP() formula with a category parameter. For example, =CG.TOP(250, "liquid-staking") instantly imports the top 250 Liquid staking tokens into your spreadsheet.

Instead of building complex API queries to filter coins by narrative, the add-in natively supports up to 600+ crypto categories from CoinGecko.

If you want to track the top Real World Assets (RWAs), simply use: =CG.TOP(250, "real-world-assets-rwa")

You can find the exact category ID by browsing the CoinGecko categories page and copying the ID from the URL.

---

Advanced: Using Power Query to Import Crypto Market Data

While the CoinGecko Add-in is the easiest way to get prices, advanced users who want to build automated, background-refreshing tables that append multiple different API endpoints together may prefer using Excel's native Power Query.

The best way to do this is by connecting Excel's "Data from Web" feature with CoinGecko's crypto API endpoint /coins/markets.

Head over to our comprehensive API documentation to retrieve this endpoint's Request URL.

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:

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-apps’ category.

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

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

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

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 Apps category is:

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.

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’. 

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

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:

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

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.

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).

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 Apps, 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’.

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 Apps, Real World Assets and Animal Racing) from the /coins/markets/ API endpoint. Click on ‘OK’ and ‘Close & Load’.

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.

Refreshing Power Query & Stocks 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.

Refreshing CoinGecko Excel Add-in Formulas

If you used the =CG() Excel formulas, simply open the CoinGecko taskpane from your Excel ribbon and click Refresh All Data. This forces all formulas in the workbook to recalculate with fresh live data from the API.

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 Stocks & Crypto Portfolio 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.

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!

---

For Day Traders: Recommended Analyst API Endpoints

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 80+ endpoints, tracks 30 million+ coins across 1,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

---

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!