如何用Excel Power Query循环调用CoinGecko API批量获取币种价格

前置准备 #

确保Sheet1中的币种ID数据整理为结构化表格：

• 表头设为CoinID，所有币种ID放在该列下（例如A列）
• 选中数据区域，点击「插入→表格」，将数据转为Excel表格（方便Power Query动态识别更新）

具体步骤 #

1. 导入币种ID到Power Query
   点击「数据→自表格/区域」，选中Sheet1的表格，进入Power Query编辑器。

2. 添加API请求URL列
   点击「添加列→自定义列」，输入以下公式（可按需修改vs_currency、from、to参数）：

   = "https://api.coingecko.com/api/v3/coins/" & [CoinID] & "/market_chart/range?vs_currency=eur&from=1392577232&to=1422577232"
   

   将新列命名为API_URL。

3. 调用API并解析JSON数据
   再次添加自定义列，输入公式获取并解析API响应：

   = Json.Document(Web.Contents([API_URL]))
   

   将新列命名为API_Response。

4. 展开价格数据

   • 点击API_Response列右侧的展开按钮，只勾选prices字段，点击确定
   • 点击新生成的prices列右侧的展开按钮，选择「展开到新行」
   • 点击展开后的prices列右侧的展开按钮，选择「拆分为列」，按默认设置拆分时间戳和价格

5. 转换时间格式并整理数据

   • 选中时间戳列，点击「转换→数据类型→日期/时间」，用公式= DateTime.FromUnixTimestamp([列名])完成转换
   • 删除不需要的辅助列（如API_URL、API_Response），重命名列名为「币种ID」「时间」「EUR价格」

6. 加载数据到Sheet2
   点击「关闭并上载→关闭并上载至」，选择Sheet2作为目标位置，设置为「仅创建连接」或直接加载为表格。后续Sheet1的币种ID更新后，只需点击「数据→全部刷新」即可同步价格数据。

完整M代码示例（可直接复制修改） #

let
    // 导入Sheet1的币种ID表格（需确保表格名称为Table1，可自行修改）
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 定义API参数，可直接修改或转为Power Query参数
    vs_currency = "eur",
    from_timestamp = 1392577232,
    to_timestamp = 1422577232,
    // 生成每个币种的API请求链接
    AddAPIURL = Table.AddColumn(Source, "API_URL", each "https://api.coingecko.com/api/v3/coins/" & [CoinID] & "/market_chart/range?vs_currency=" & vs_currency & "&from=" & Number.ToText(from_timestamp) & "&to=" & Number.ToText(to_timestamp)),
    // 调用API并解析响应
    GetAPIData = Table.AddColumn(AddAPIURL, "API_Response", each Json.Document(Web.Contents([API_URL]))),
    // 展开价格数组
    ExpandPrices = Table.ExpandRecordColumn(GetAPIData, "API_Response", {"prices"}, {"prices"}),
    // 将价格列表展开为多行
    ExpandRows = Table.ExpandListColumn(ExpandPrices, "prices"),
    // 拆分时间戳和价格为独立列
    SplitPriceData = Table.SplitColumn(ExpandRows, "prices", Splitter.SplitByNothing(), {"Timestamp", "EUR Price"}),
    // 转换时间戳为日期时间格式
    ConvertTimestamp = Table.TransformColumns(SplitPriceData, {{"Timestamp", DateTime.FromUnixTimestamp, type datetime}}),
    // 整理并重命名列
    CleanAndRename = Table.RenameColumns(Table.SelectColumns(ConvertTimestamp, {"CoinID", "Timestamp", "EUR Price"}),{{"CoinID", "币种ID"}, {"Timestamp", "时间"}, {"EUR Price", "EUR价格"}})
in
    CleanAndRename

注意事项 #

• API请求限制：CoinGecko免费API每分钟最多允许50次请求，若币种数量较多，可在GetAPIData步骤中添加延迟（如Thread.Sleep(1000)控制每秒1次请求，需在Power Query启用相关设置）
• 隐私权限：首次调用若弹出隐私提示，需将api.coingecko.com设置为「公共」数据源（路径：文件→选项和设置→数据源设置→编辑权限）

内容的提问来源于stack exchange，提问作者PyMarv