How to draw live crypto and stock prices in Excel

Having access to real-time price data is essential to making informed decisions. Not surprisingly, many financial analysts and traders use Microsoft Excel to track their portfolio, stock and crypto performance, asset details, profit and loss, return on investment (ROI), among other metrics. To conveniently retrieve live price data, we’ll use Excel’s native stock integration feature and CoinGecko’s crypto API.

This step-by-step guide will walk you through:

How to import real-time stock data into Excel How to import live crypto data into Excel (including coins in trending categories) Bonus: Set up a P&L tracker for your investment portfolio

Let’s jump in!

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

Get real-time stock data in Excel

First, create a new spreadsheet on Microsoft Excel, and name it ‘Shares’. In cell A1, list stocks that you currently hold or that are on your watch list. This is the tab where you will enter all stock related data using Excel’s native data integration.

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

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

Select all fields you want information about – for each selection a new column will appear and be filled accordingly. In this guide, we’ve kept it simple and included just four columns: Ticker, Price, Change, Price Change % and Market Cap.

How to Import Live Crypto Prices into Excel Spreadsheets

The easiest way to import live crypto prices is to use Excel’s “Data from Web” function and connect it to CoinGecko’s crypto API endpoint /coins/markets. To locate this endpoint’s request URL, refer to our comprehensive API documentation.

‘/coins/markets’ is one of the most widely used crypto API endpoints that traders, developers, projects and analysts rely on to retrieve real-time crypto price data. With only two API calls on this endpoint, you can retrieve data from the top 500 cryptocurrencies listed on CoinGecko, which is typical enough for most. To import data for coins outside the top 500 or smaller market cap cryptocurrencies, simply adjust the ‘Page’ parameters accordingly (each page displays 250 coin data).

Expand the switch on /coins/markets, click ‘Try it’ and fill in the parameters following the API playground commands.

Leave the ‘ids’ parameter blank and add the following entries:

Adjust the other fields according to your preferences. In this example we have included:

Vs_currency: usd Order: market_cap_desc (sort market cap in descending order) Sparkline: true Price_change_percentage: 1h,24h Local: en Precision: 3

Tip: If you want to import coin data for specific crypto categories rather than pulling in an entire list of top 250 or 500 cryptocurrencies, enter the respective category ID in the ‘category’ parameter field.

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

Click the ‘Export’ 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 following 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 main 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=3&precision=en

Let’s go back to our Excel workbook and navigate to ‘Data’. This time select ‘From the web’.

If ‘From the web’ is not available in your navigation bar, you can find it under Data > From other sources > From the web. If you are a Mac OS user on Excel, you can rely on CryptoSheets instead, as this feature only applies to Windows OS users.

Clicking ‘From the Web’ will bring up a pop-up window. Make sure the switch is set to ‘Basic’, copy and paste the first Request URL into the input field and select ‘OK’.

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

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

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_apcig_AP

Once loaded, Excel’s Power Query Editor appears in a new window. To avoid random query strings, you can choose to rename the query – in this example we’ve renamed it to ‘coins/markets1-250’.

Click on ‘To Table’ in the top right corner and select ‘OK’. You do not need to select or enter a delimiter.

Select the expander icon next to Column1 and a drop-down list will appear. As a default, all columns will be selected. You can also uncheck the box below that says ‘Use original column name as prefix’ so that columns will be marked clean.

Select ‘OK’ and the data will be expanded into several columns. Then click on ‘Close and Load’ and the data will be filled in a new worksheet, which we will name as ‘Top 500 Crypto + Categories’ (we will add more cryptocurrencies in the subsequent steps).

Repeat this process, but this time use the second request URL with the ‘Page’ parameter set to ‘2’. Instead of immediately closing and loading this query, we will also enter specific crypto categories data in the same way. Given their recent rally, we have entered cryptocurrencies from Telegram Bots, Real World Assets and Animal Racing categories.

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

A dialog box appears and you can switch to ‘Three or more tables’ and select the tables to add accordingly. It 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 ‘OK’ and ‘Close and Load’.

In just a few clicks and less than 5 minutes, you now have a real-time, functional Excel workbook importing stock and crypto price data! Specific to crypto, you have a consolidated list of top 500 cryptocurrencies data and coins from trending categories you are watching.

Currently refreshing the data

On Excel, you can change the refresh frequency to your preference by going to Data > Refresh All > Connection Properties. Uncheck ‘Enable background refresh’ and ‘Refresh every 30 minutes’ if you want to refresh manually and preserve the number of API calls made, or opt for a 10-min auto-refresh as shown in the screenshot below.

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

Did you enjoy this guide? Be sure to check out this master tutorial that covers multiple endpoints for extracting crypto prices in Excel.

How to Create a Crypto Tracker on Excel Spreadsheets

With real-time stock and crypto price data 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 customizes 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 that uses 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 coin images to life!

Add formulas accordingly in the gray columns:

Holdings Value – Multiply Holdings # by Current Price. It depicts how much value your stocks and crypto holdings are worth, in fiat currency. P&L – The difference between Holding Value and Total Invest, essentially your unrealized profit or loss, if the position was closed at the 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 > Featured Charts. You are ready!

For Day Traders: Recommended Analyzer API Endpoints

Advanced traders who want access to more API endpoints, historical prices and avoid getting rate-capped can 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 every month.

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

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

Want 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!

Julia Ng

Julia leads Growth at CoinGecko and is passionate about onboarding more women on Web3. That said, she is generally bad at timing the market, so she DCAs for safety. Follow the author on Twitter @ngxinyajulia