قیمت لحظه ای طلا در اکسل و گوگل شیت؛ اتصال به API بدون برنامه‌نویسی پیچیده

حسابداران، کارشناسان بازرگانی و طلافروشانی که محاسباتشان در اکسل است، معمولاً هر صبح نرخ دلار و طلا را از یک سایت کپی و در فایل خود وارد می‌کنند. این کار هم وقت می‌گیرد و هم خطای تایپی می‌سازد. در این آموزش قیمت لحظه ای طلا در اکسل و گوگل شیت را مستقیم از API می‌خوانیم؛ بدون نیاز به دانش برنامه‌نویسی جدی.

این آموزش بخشی از راهنمای کامل API قیمت ارز و طلا است.

چرا IMPORTDATA و IMPORTXML کافی نیست؟

گوگل شیت تابع‌هایی مثل IMPORTDATA و IMPORTXML دارد که داده را از یک آدرس می‌خوانند. اما این تابع‌ها نمی‌توانند هدر سفارشی (مثل کلید API) بفرستند و برداشت HTML سایت‌های دیگر هم همان مشکلات اسکرپینگ را دارد (اسکرپ سایت قیمت یا API). راه درست، یک تابع سفارشی کوچک با Apps Script است.

گوگل شیت: تابع سفارشی =EZRATE()

1. فایل گوگل شیت را باز کنید و از منوی Extensions گزینهٔ Apps Script را بزنید.
2. کد زیر را جایگزین کد پیش‌فرض کنید و ذخیره کنید.
3. از منوی Project Settings (چرخ‌دنده) در بخش Script Properties یک ویژگی با نام EZRATE_KEY و مقدار کلید خود بسازید.
4. به شیت برگردید و در یک خانه بنویسید =EZRATE("usd"). بار اول گوگل اجازهٔ دسترسی به اینترنت را می‌پرسد.

/**
 * قیمت لحظه‌ای از EZRate.
 * @param {string} symbol نماد: usd, eur, g18, emami, usdt…
 * @param {string} side   "sell" (پیش‌فرض) یا "buy"
 * @customfunction
 */
function EZRATE(symbol, side) {
  side = side || "sell"
  const rates = ezrateAll_()
  const r = rates[String(symbol).toLowerCase()]
  if (!r) throw new Error("نماد ناشناخته: " + symbol)
  return r[side]
}

/** همهٔ نرخ‌ها، با کش ۵ دقیقه‌ای تا هر خانه درخواست جدا نسازد */
function ezrateAll_() {
  const cache = CacheService.getScriptCache()
  const hit = cache.get("ezrate_all")
  if (hit) return JSON.parse(hit)
  const key = PropertiesService.getScriptProperties().getProperty("EZRATE_KEY")
  const res = UrlFetchApp.fetch("https://api.ezrate.me/v1/rates?symbols=usd,eur,aed,try,usdt,g18,g24,mesghal,ounce,emami,bahar,nim,rob,gerami", {
    headers: { "X-API-Key": key }, muteHttpExceptions: true })
  if (res.getResponseCode() !== 200) throw new Error("EZRate: HTTP " + res.getResponseCode())
  const map = {}
  JSON.parse(res.getContentText()).data.forEach(x => (map[x.symbol] = { buy: x.buy, sell: x.sell, at: x.updated_at }))
  cache.put("ezrate_all", JSON.stringify(map), 300)
  return map
}

نکتهٔ مهم کد: همهٔ نرخ‌ها با یک درخواست گرفته و ۵ دقیقه کش می‌شوند. اگر ۲۰۰ خانه از =EZRATE() استفاده کنند، باز هم فقط یک درخواست ارسال می‌شود.

به‌روزرسانی خودکار

تابع‌های سفارشی گوگل شیت فقط وقتی دوباره محاسبه می‌شوند که ورودی‌شان تغییر کند. برای تازه شدن دوره‌ای، از Triggers در Apps Script یک تریگر زمانی (هر ۱۵ دقیقه) بسازید که یک خانهٔ کمکی را عوض کند، یا ساده‌تر: در ورودی تابع از یک خانه که زمان فعلی را دارد استفاده کنید. برای بیشتر کاربردهای حسابداری، تازه شدن هنگام باز کردن فایل کافی است.

اکسل: Power Query

در اکسل ۲۰۱۶ به بعد و Microsoft 365، Power Query می‌تواند هر API با خروجی JSON را بخواند:

1. از تب Data گزینهٔ Get Data › From Other Sources › Blank Query را بزنید.
2. در Power Query Editor از تب Home گزینهٔ Advanced Editor را باز کنید و کد زیر را بچسبانید.
3. کلید خود را به‌جای YOUR_KEY بنویسید (یا آن را به یک پارامتر Power Query تبدیل کنید).
4. Close & Load را بزنید؛ جدول نرخ‌ها در یک شیت جدید ساخته می‌شود.

let
    Source = Json.Document(Web.Contents("https://api.ezrate.me/v1/rates",
        [Query = [symbols = "usd,eur,aed,g18,mesghal,emami,bahar,usdt", lang = "fa"],
         Headers = [#"X-API-Key" = "YOUR_KEY"]])),
    Data = Source[data],
    Table = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1",
        {"symbol", "name", "buy", "sell", "change_pct", "updated_at"}),
    Typed = Table.TransformColumnTypes(Expanded,
        {{"buy", Int64.Type}, {"sell", Int64.Type}, {"change_pct", type number}})
in
    Typed

برای تازه کردن، Data › Refresh All را بزنید یا از Query Properties گزینهٔ «Refresh every 15 minutes» و «Refresh data when opening the file» را فعال کنید. حالا در فرمول‌های خود با VLOOKUP یا XLOOKUP قیمت هر نماد را از این جدول بردارید؛ مثلاً =XLOOKUP("g18";Table1[symbol];Table1[sell]).

نمونهٔ کاربردی: محاسبهٔ ارزش موجودی طلا و ارز

برای ارزش‌گذاری دارایی‌ای که در اختیار دارید، نرخ خرید (buy) منطقی‌تر است؛ چون اگر امروز بفروشید همین را می‌گیرید.

امنیت کلید در فایل‌های اشتراکی

• در گوگل شیت، کلید را در Script Properties بگذارید، نه داخل کد یا یک خانهٔ شیت. اما توجه کنید هر کسی دسترسی ویرایش به فایل دارد، می‌تواند Apps Script را ببیند.
• فایل اکسل حاوی کوئری با کلید را برای افراد بیرون از سازمان نفرستید؛ قبل از ارسال، کوئری را حذف یا مقادیر را Paste Values کنید.
• برای فایل‌های تیمی یک کلید جدا بسازید تا اگر لازم شد، فقط همان را باطل کنید.

نسخهٔ انگلیسی همین آموزش در Live Exchange Rates in Google Sheets and Excel آمده است. اگر تاریخچهٔ روزانه هم در اکسل لازم دارید، دیتای تاریخی قیمت دلار و طلا را ببینید.

نرخ‌های لحظه‌ای مرتبط با این مطلب: قیمت طلای ۱۸ عیار، قیمت دلار، قیمت سکه امامی.