What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Yes—you can build a refreshable Excel table for several cryptocurrencies with one Power Query request. Put CoinGecko IDs such as bitcoin, ethereum, and solana in an Excel table, call CoinGecko’s /simple/price endpoint through Data > From Web, then turn the returned JSON record into rows and columns. The result can include price, market cap, 24-hour volume, 24-hour percentage change, and a UTC update timestamp.
This produces current data returned when Excel refreshes—not a tick-by-tick exchange feed. Values can reflect provider update intervals, caching, network delay, plan limits, and CoinGecko’s aggregated market sources.
What you need before starting
- Excel with Power Query (Get & Transform) and the Web connector. Microsoft documents the connector and menu paths at learn.microsoft.com/en-us/power-query/connectors/web/web.
- An internet connection.
- A list of CoinGecko coin IDs.
- An Excel table named
CryptoCoinswith one ID per row. - An API key only if your CoinGecko access level or request volume requires one.
Menu labels vary between Microsoft 365, perpetual Excel releases, Mac, Windows, and web experiences. The walkthrough uses the desktop Power Query workflow; confirm connector and credential support for your edition.
Create the editable coin list
On a worksheet, create a table with a column named CoinID. Select the range and choose Insert > Table, then set the table name to CryptoCoins under Table Design.
#1 Best Overall
- Get NVMe solid state performance with up to 1050MB/s read and 1000MB/s write speeds in a portable, high-capacity drive(1) (Based on internal testing; performance may be lower depending on host device & other factors. 1MB=1,000,000 bytes.)
- Up to 3-meter drop protection and IP65 water and dust resistance mean this tough drive can take a beating(3) (Previously rated for 2-meter drop protection and IP55 rating. Now qualified for the higher, stated specs.)
- Use the handy carabiner loop to secure it to your belt loop or backpack for extra peace of mind.
- Help keep private content private with the included password protection featuring 256‐bit AES hardware encryption.(3)
- Easily manage files and automatically free up space with the SanDisk Memory Zone app.(5). Non-Operating Temperature -20°C to 85°C
| CoinID | DisplayName (optional) |
|---|---|
| bitcoin | Bitcoin |
| ethereum | Ethereum |
| solana | Solana |
| cardano | Cardano |
| dogecoin | Dogecoin |
Use CoinGecko IDs rather than relying on ticker symbols. CoinGecko accepts IDs, symbols, and names, but symbols can identify more than one asset. Find supported IDs with CoinGecko’s coin-list documentation at docs.coingecko.com/docs/keyless-public-api or the asset’s CoinGecko URL slug. Keep DisplayName separate; the query uses the ID as the reliable key.
Choose the endpoint
| Need | Endpoint |
|---|---|
| Current prices for a selected portfolio | /simple/price |
| Price plus market cap, volume, and 24-hour change | /simple/price with include parameters |
| Rankings, supply, highs/lows, and broad market screening | /coins/markets |
| Historical chart data | A coin-specific historical or market-chart endpoint |
| A token identified by contract address | CoinGecko’s contract-address price endpoint |
The basic request is:
https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum,solana&vs_currencies=usd&include_24hr_change=true&include_market_cap=true&include_24hr_vol=true&include_last_updated_at=true
CoinGecko documents parameters and response fields at docs.coingecko.com/reference/simple-price. The ids value accepts multiple comma-separated IDs, so one request is preferable to a separate request for every row.
Open Power Query’s From Web connector
- Select any cell in the workbook.
- Choose Data > From Web. Some builds expose the same route as Data > Get Data > From Other Sources > From Web.
- Open the Advanced option if Excel asks for a URL. A placeholder URL is sufficient because the final request will be defined in M.
- When Power Query Editor opens, select Home > Advanced Editor.
- Replace the generated expression with the query below and select Done.
Microsoft’s Excel import guidance is at support.microsoft.com/en-US/Excel/import-data-from-data-sources-power-query.
Paste this dynamic Power Query M code
The query reads the editable table, removes blank and duplicate IDs, sends one batched request, parses the JSON, expands the nested records, converts the Unix timestamp to UTC, and applies useful data types.
Rank #2
- Solid state performance with up to 800MB/s read speeds in a portable drive. (Based on internal testing; performance may be lower depending on host device, interface, usage conditions and other factors. 1MB=1,000,000 bytes.)
- Back up your content and memories on a storage solution that fits seamlessly into your mobile lifestyle.
- Take it with you on your adventures—up to two-meter drop protection means this durable drive can take a beating. (Based on internal testing.)
- Secure it to your belt loop or backpack for extra peace of mind thanks to the tough rubber hook.
- From Sandisk, a brand professional photographers trust to take on assignments.
let
CoinTable =
Excel.CurrentWorkbook(){[Name = "CryptoCoins"]}[Content],
CleanCoins =
Table.SelectRows(
CoinTable,
each [CoinID] <> null and Text.Trim(Text.From([CoinID])) <> ""
),
CoinIDs =
List.Transform(
CleanCoins[CoinID],
each Text.Lower(Text.Trim(Text.From(_)))
),
DistinctCoinIDs = List.Distinct(CoinIDs),
CheckedIDs =
if List.Count(DistinctCoinIDs) = 0
then error "CryptoCoins must contain at least one valid CoinID."
else DistinctCoinIDs,
IDsParameter = Text.Combine(CheckedIDs, ","),
Response =
Web.Contents(
"https://api.coingecko.com",
[
RelativePath = "api/v3/simple/price",
Query = [
ids = IDsParameter,
vs_currencies = "usd",
include_market_cap = "true",
include_24hr_vol = "true",
include_24hr_change = "true",
include_last_updated_at = "true"
],
Timeout = #duration(0, 0, 2, 0)
]
),
Source = Json.Document(Response),
CoinRows = Record.ToTable(Source),
RenamedCoinColumn =
Table.RenameColumns(
CoinRows,
{{"Name", "CoinID"}, {"Value", "MarketData"}}
),
ExpandedMarketData =
Table.ExpandRecordColumn(
RenamedCoinColumn,
"MarketData",
{
"usd",
"usd_market_cap",
"usd_24h_vol",
"usd_24h_change",
"last_updated_at"
},
{
"Price_USD",
"MarketCap_USD",
"Volume_24h_USD",
"Change_24h_Percent",
"LastUpdated_UNIX"
}
),
AddedLastUpdatedUTC =
Table.AddColumn(
ExpandedMarketData,
"LastUpdated_UTC",
each
if [LastUpdated_UNIX] = null
then null
else #datetime(1970, 1, 1, 0, 0, 0)
+ #duration(0, 0, 0, Number.From([LastUpdated_UNIX])),
type datetime
),
TypedColumns =
Table.TransformColumnTypes(
AddedLastUpdatedUTC,
{
{"CoinID", type text},
{"Price_USD", type number},
{"MarketCap_USD", type number},
{"Volume_24h_USD", type number},
{"Change_24h_Percent", type number},
{"LastUpdated_UNIX", Int64.Type},
{"LastUpdated_UTC", type datetime}
}
),
SortedRows =
Table.Sort(
TypedColumns,
{
{"MarketCap_USD", Order.Descending},
{"CoinID", Order.Ascending}
}
)
in
SortedRowsExcel.CurrentWorkbook() reads tables, named ranges, and dynamic arrays from the current workbook; Microsoft documents it at learn.microsoft.com/en-us/powerquery-m/excel-currentworkbook. Json.Document parses the web response, while Record.ToTable changes the top-level JSON object into a two-column table. Microsoft’s web-function reference is at learn.microsoft.com/en-in/powerquery-m/web-contents.
Why Record.ToTable is essential
A typical response is a record keyed by coin ID:
{
"bitcoin": {
"usd": 67187.3358936566,
"usd_market_cap": 1317802988326.25,
"usd_24h_vol": 31260929299.5248,
"usd_24h_change": 3.63727894677354,
"last_updated_at": 1711356300
},
"ethereum": {
"usd": 3400.12,
"usd_market_cap": 408000000000,
"usd_24h_vol": 18500000000,
"usd_24h_change": -1.24,
"last_updated_at": 1711356300
}
}Power Query first sees one record, not ordinary rows. Record.ToTable creates CoinID and MarketData; expanding MarketData produces the numeric columns.
Load and format the result
- In Power Query Editor, check that one row appears for each returned coin.
- Choose Home > Close & Load (or Close & Load To… to select a destination).
- Format
Price_USD,MarketCap_USD, andVolume_24h_USDas numbers or currency according to your workbook’s reporting convention. - Format
Change_24h_Percentas a number with a percent sign only if you first account for the API’s percentage-point value. A value such as3.637means approximately 3.637 percent; applying Excel’s percent format directly would display 363.7 percent. - Keep
LastUpdated_UTClabelled as UTC unless you deliberately convert it to a local timezone.
Refresh prices without rebuilding the query
Edit the CryptoCoins table whenever you want to add or remove an asset, then choose Data > Refresh All. A single batched request is gentler on rate limits than a custom column that calls the API once per row.
Refreshable does not mean exchange-grade real time. CoinGecko’s update and cache behavior, your plan’s allowance, network latency, and Excel’s own refresh behavior determine when a value changes. Use LastUpdated_UTC to show the timestamp supplied for the asset, not the time the workbook happened to finish loading.
Add currencies, precision, or optional fields
Multiple quote currencies
Change the query value to:
vs_currencies = "usd,eur,gbp"The response then contains fields such as usd, eur, and gbp, plus currency-specific change, market-cap, and volume fields. Add the exact field names to the Table.ExpandRecordColumn list and provide matching output names.
Rank #3
- Easily store and access 2TB to content on the go with the Seagate Portable Drive, a USB external hard drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Precision
Add a parameter such as:
precision = "4"CoinGecko supports full, zero decimal places, and higher decimal-place options. Precision is useful for display, but do not round the source value prematurely when calculating portfolio values, performance, or tax records.
Interpret the optional metrics correctly
- Price: the requested quote-currency value returned at refresh.
- Market cap: a market-data measure, not the value of your holding.
- 24-hour volume: the provider’s aggregate volume field.
- 24-hour change: percentage movement over the prior 24 hours, not a freshness timestamp.
- Last updated: a Unix timestamp supplied by the API and converted by the query to UTC.
Handle missing IDs instead of silently losing rows
An invalid, unavailable, or delisted ID may simply be absent from the response. Compare requested IDs with returned IDs in a separate diagnostic query:
Recommended Free Tools
MissingCoins =
Table.NestedJoin(
Table.Distinct(Table.SelectColumns(CleanCoins, {"CoinID"})),
{"CoinID"},
Table.SelectColumns(ExpandedMarketData, {"CoinID"}),
{"CoinID"},
"Matches",
JoinKind.LeftAnti
)Load MissingCoins to a small warning table. This makes a dropped asset visible instead of treating a shorter output as a successful portfolio refresh.
Secure an API key when your plan requires one
Do not put a real secret in an article, worksheet, shared workbook, or literal M expression. A workbook can expose keys through Advanced Editor code, query parameters, connection metadata, diagnostics, cloud version history, or screenshots.
For a CoinGecko Pro request, define ApiKey as a Power Query parameter or managed credential and use the documented header pattern:
Rank #4
- NEARLY 2X FASTER THAN OUR PREVIOUS GENERATION(8) – move 1,000 high-res photos in under 60 seconds(6) with up to 2000MB/s transfer speeds(2).
- IP65 RATING AND UP TO 3M DROP PROTECTION(3) – protects against spills and drops.
- POCKET-SIZED – fits easily in pockets and small bags.
- SPACE TO OWN YOUR AI CONTENT – speed and capacity to download your high-res clips and photo edits.
- 256-BIT AES ENCRYPTION(4) – helps keep private files secure with password protection.
Response =
Web.Contents(
"https://pro-api.coingecko.com",
[
RelativePath = "api/v3/simple/price",
Query = [
ids = IDsParameter,
vs_currencies = "usd",
include_market_cap = "true",
include_24hr_vol = "true",
include_24hr_change = "true",
include_last_updated_at = "true"
],
Headers = [#"x-cg-pro-api-key" = ApiKey],
Timeout = #duration(0, 0, 2, 0)
]
)CoinGecko’s API reference documents the Pro header at docs.coingecko.com/reference/simple-price. Authentication differs by product and plan; some access methods specify a query-string key instead. Follow the current provider documentation rather than assuming one method is universal. Microsoft describes credential handling, headers, query parameters, and ApiKeyName at learn.microsoft.com/en-in/powerquery-m/web-contents.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →If Excel reports an authentication problem, open Data > Get Data > Data Source Settings, clear or edit the saved permission for the CoinGecko domain, and reconnect using the appropriate authentication type. Revoke a key immediately if it has been shared publicly.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Troubleshoot common failures
“We cannot convert the value null to type Text”
A blank cell reached the text conversion. The supplied query filters null and empty values before building the list. Check that the column is really named CoinID and that the table is really named CryptoCoins.
401 or 403 response
- Confirm the base URL matches your plan.
- Check the header spelling and parameter name required by that plan.
- Ensure
ApiKeyis a parameter containing a real credential, not placeholder text. - Clear stale permissions under Data Source Settings.
429 rate-limit response
Reduce refresh frequency, keep one batched request, and check the allowance and caching rules for your current CoinGecko access method. Avoid one web call per coin.
Invalid ID or fewer rows than expected
Verify the CoinGecko ID, not the ticker, and run the missing-coin anti-join. A successful HTTP response does not guarantee that every requested ID was returned.
Best Value
- Easily store and access 5TB of content on the go with the Seagate portable drive, a USB external hard Drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Expansion errors
The expansion list must match fields requested by the endpoint. If you remove an include flag, remove its field from Table.ExpandRecordColumn; if you add currencies, add their returned field names.
Old or apparently unchanged values
Refresh the query and inspect LastUpdated_UTC. Power Query can reuse cached results during development; Microsoft documents cache and retry controls, including IsRetry, as troubleshooting options rather than settings to turn on routinely.
Empty input
The CheckedIDs step intentionally stops with a clear error when no valid IDs remain. Add at least one nonblank CoinGecko ID.
When /coins/markets is the better choice
Use /coins/markets when you need names, symbols, market-cap rank, circulating supply, highs and lows, all-time metrics, or a broad market screen. It returns a list of records rather than a record keyed by ID, so the transformation starts by expanding a list, not by using Record.ToTable. CoinGecko’s support guidance states that this endpoint is paginated and supports a maximum of 250 coins per call: support.coingecko.com/hc/en-us/articles/4538868138905-Can-I-batch-call-multiple-tokens-current-price-data. For a fixed personal portfolio, /simple/price remains the smaller and easier model.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Power Query versus CoinGecko’s Excel add-in
| Power Query | Official CoinGecko Excel add-in |
|---|---|
| Editable table-driven input and one batched request | Formula-driven setup such as =CG.PRICE(id) |
| Custom transformations, joins, validation, and multiple endpoints | Faster for straightforward worksheet use |
| More setup and M-code maintenance | Less control over complex normalization |
| Useful for a governed refresh pipeline inside a workbook | Taskpane refresh actions and familiar formulas |
The add-in’s documented functions include =CG.PRICE(id), =CG.HISTORY(id, date), and =CG.TOP(limit, [category]). See docs.coingecko.com/docs/excel. It still has its own installation, authentication, and plan requirements.
Quick Recap
Know the limits of a workbook price table
- CoinGecko aggregates market data; the value may not equal the quote at your chosen exchange.
/simple/pricesupplies current values, not a historical series.- Coverage, update timing, rate limits, and authentication depend on the asset and current API plan.
- Power Query is not a substitute for a server-side market-data database, high-frequency feed, audit trail, or multi-user governance system.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
