# اکسل و Power Query

> دو راه آوردن دادهٔ بورس به اکسل با روشا؛ دانلود CSV رایگان یک‌ساله از صفحهٔ هر نماد، و اتصال Power Query به API با سرآیند Authorization و کد M آماده.
> Source: https://poolick.ir/docs/guides/excel/

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

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

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

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

1. صفحهٔ نماد را باز کنید. نماد را از [فهرست نمادها](/symbols/) پیدا کنید.
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

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

```powerquery
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](/docs/api/) است.

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

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