اکسل و Power Query

برای آوردن دادهٔ روشا به اکسل دو راه دارید: فایل CSV رایگان یک‌ساله را از صفحهٔ نماد دانلود کنید، یا با Power Query مستقیم به API وصل شوید تا جدول با یک کلیک تازه شود. راه اول کلید نمی‌خواهد؛ راه دوم کلید API می‌خواهد.

	CSV رایگان	Power Query و API
کلید	لازم نیست	لازم است (پلن رایگان کافی است)
بازه	یک سال اخیر	کل سابقه، تا ۵٬۰۰۰ ردیف در هر درخواست
تازه‌سازی	دانلود دوباره	دکمهٔ Refresh در اکسل
داده	قیمت تعدیل‌شدهٔ روزانهٔ یک نماد	هر endpoint روشا

راه اول: CSV رایگان از صفحهٔ نماد

هر نماد یک صفحهٔ رایگان در /symbols/{ins_code}/ دارد که فایل CSV یک سال اخیر را می‌دهد. برای فولاد: /symbols/46348559193224090/.

1. صفحهٔ نماد را باز کنید. نماد را از فهرست نمادها پیدا کنید.
2. دکمهٔ دانلود CSV را بزنید.
3. فایل را در اکسل باز کنید. فایل UTF-8 با BOM است و اکسل متن فارسی را درست نشان می‌دهد.

ستون‌های فایل:

ستون	معنا
date	تاریخ میلادی
jalali_date	تاریخ شمسی
open، high، low، close، last	قیمت‌های تعدیل‌شده به ریال
volume	حجم معاملات
value	ارزش معاملات به ریال
trades	تعداد معاملات
close_unadjusted	قیمت پایانی خام (تعدیل‌نشده) به ریال

دانلود رایگان سقف روزانه دارد (تا آبان ۱۴۰۵: ۲۰ فایل در روز برای هر IP). برای دانلود بیشتر یا خودکار از راه دوم استفاده کنید.

راه دوم: Power Query با کلید API

Power Query می‌تواند درخواست را با سرآیند Authorization بفرستد و پاسخ JSON را به جدول تبدیل کند. گام‌های زیر برای اکسل ویندوز (Microsoft 365 و نسخه‌هایی که منوی Get Data دارند) نوشته شده است.

گام‌به‌گام

1. در اکسل به زبانهٔ Data بروید و Get Data ← From Other Sources ← From Web را بزنید.

2. در پنجرهٔ From Web گزینهٔ Advanced را انتخاب کنید.

3. در URL parts این نشانی را بگذارید:

   https://poolick.ir/v1/instruments/46348559193224090/daily?adjusted=true&limit=5000

4. در HTTP request header parameters نام سرآیند را Authorization و مقدار آن را Bearer rosha_xxxxxxxx_your-secret بگذارید (با کلید خودتان) و OK را بزنید.

5. اگر اکسل نوع دسترسی را پرسید، Anonymous را انتخاب کنید و Connect را بزنید. کلید در سرآیند فرستاده می‌شود.

6. ویرایشگر Power Query باز می‌شود. از زبانهٔ Home گزینهٔ Advanced Editor را بزنید، متن را با کد M زیر جایگزین کنید و Done را بزنید.

7. Close & Load را بزنید. جدول در برگه قرار می‌گیرد.

برای تازه‌سازی، روی جدول راست‌کلیک کنید و Refresh را بزنید.

کد M

این کد سابقهٔ تعدیل‌شدهٔ فولاد را می‌گیرد و ستون‌ها را نوع‌دهی می‌کند. سه مقدار بالای کد را عوض کنید: کلید، شناسهٔ نماد و تعداد ردیف.

let
    ApiKey = "rosha_xxxxxxxx_your-secret",
    InsCode = "46348559193224090",
    Limit = "5000",

    Response = Json.Document(
        Web.Contents(
            "https://poolick.ir",
            [
                RelativePath = "v1/instruments/" & InsCode & "/daily",
                Query = [adjusted = "true", limit = Limit],
                Headers = [Authorization = "Bearer " & ApiKey]
            ]
        )
    ),
    Rows = Table.FromRecords(Response[rows]),
    Typed = Table.TransformColumnTypes(
        Rows,
        {
            {"d", type date},
            {"open", type number},
            {"high", type number},
            {"low", type number},
            {"close", type number},
            {"last", type number},
            {"prev_close", type number},
            {"volume", type number},
            {"value", type number},
            {"trades", Int64.Type},
            {"jdate", type text}
        }
    )
in
    Typed

InsCode در این کد متن است و باید متن بماند.

برای endpointهای دیگر چه چیزی عوض می‌شود؟

RelativePath، Query و نام فیلدی که ردیف‌ها در آن است.

داده	RelativePath	ردیف‌ها کجای پاسخ‌اند
سابقهٔ روزانهٔ نماد	v1/instruments/<ins_code>/daily	Response[rows]
حقیقی و حقوقی	v1/instruments/<ins_code>/client-type	خود Response (فهرست)
سابقهٔ شاخص	v1/indices/<ins_code>/daily	خود Response (فهرست)
سابقهٔ صندوق	v1/funds/<reg_no>/history	Response[rows]
فهرست صندوق‌ها	v1/funds	Response[items]

برای endpointهایی که فهرست ساده برمی‌گردانند، خط Rows را این‌طور بنویسید: Rows = Table.FromRecords(Response). ستون‌های Table.TransformColumnTypes را هم با فیلدهای همان endpoint تطبیق دهید؛ فهرست فیلدها در مرجع API است.

نکته‌های اکسل

• ins_code را متن نگه دارید. اکسل عددها را تا ۱۵ رقم دقیق نگه می‌دارد و ins_code بلندتر است. اگر ستونی از ins_code دارید، نوع آن را در Power Query روی Text بگذارید و در برگه قالب خانه را Text کنید.
• ریال: همهٔ مبالغ به ریال است. برای تومان یک ستون محاسباتی با تقسیم بر ۱۰ بسازید.
• تاریخ شمسی: ستون jdate (در API) یا jalali_date (در CSV) تاریخ شمسی را به شکل متن دارد.
• خانهٔ خالی: در روزهای بدون معامله open، high و low خالی (null) است.
• سهمیه: هر Refresh برای هر query یک درخواست از سهمیهٔ روزانهٔ کلید کم می‌کند. تازه‌سازی خودکار با فاصلهٔ کوتاه لازم نیست؛ دادهٔ روزانه یک بار در روز، ساعت ۱۸:۱۵ تهران، تازه می‌شود. به‌روزرسانی داده‌ها.
• کلید در فایل: کلید داخل کد M در فایل اکسل ذخیره می‌شود. فایلی را که کلید دارد با دیگران به اشتراک نگذارید؛ اگر گذاشتید، کلید را در پنل بچرخانید.
• خطای دسترسی: اگر Power Query خطای 401 یا 403 داد، کلید و پلن را بررسی کنید. معنای کدها در خطاها است.