محاسبه میانگین محدوده واقعی (ATR) در اکسل

این راهنمای دقیق برای محاسبه میانگین محدوده واقعی (ATR) در اکسل است. ابتدا محدوده واقعی و سپس ATR را به عنوان میانگین متحرک محدوده واقعی محاسبه می کنیم. ما هر سه روش رایج محاسبه ATR را پوشش خواهیم داد؟روش هموارسازی ساده، نمایی و اصلی وایلدر.

برای این محاسبات به مهارت های اکسل پیشرفته نیاز ندارید؟آنها فقط از توابع نسبتاً ساده اکسل مانند MAX یا AVERAGE استفاده می کنند.

داده های تاریخی

برای محاسبه میانگین محدوده واقعی، به تاریخچه بالا، پایین و بسته برای هر روز یا نوار نیاز دارید. اگر داده‌های خود را با فرمت OHLC دارید، آن‌ها را در یک صفحه‌گسترده جدید اکسل در ستون‌های A (تاریخ/زمان)، B (باز؟ واقعاً مورد نیاز نیست)، C (بالا)، D (کم)، E (بستن) قرار دهید. برچسب های ستون را در ردیف 3 و داده های واقعی را در زیر قرار دهید، از ردیف 4 شروع کنید.

من از 1 ژوئن تا 31 آگوست 2017 از قیمت های روزانه برای ETF GLD استفاده خواهم کرد. اگر می خواهید این آموزش را دقیقاً با همان داده ها دنبال کنید و بررسی کنید که نتایج شما درست است، می توانید داده ها را از Yahoo Finance دانلود کنید (نماد GLD است)). توجه داشته باشید که من قیمت ها را به دو رقم اعشار گرد کرده ام تا با عدم دقت داده های یاهو مقابله کنم.

شما همچنین می توانید ATR را برای فرکانس های دیگر، مانند هفتگی، ماهانه یا درون روز محاسبه کنید؟فرمول ها یکسان استمن از "day" و "bar" به جای یکدیگر برای اشاره به یک ردیف در سراسر این آموزش استفاده خواهم کرد.

محاسبه محدوده واقعی

قبل از خود ATR ابتدا باید محدوده واقعی را برای هر روز محاسبه کنیم، زیرا ATR میانگین متحرک آن است. فرمول کاملا ساده است؟محدوده واقعی بزرگترین تفاوت قیمت از سه تفاوت زیر است:

• زیاد منهای کم (محدوده سنتی)
• بالا منهای بسته قبلی
• بسته قبلی منهای کم

برای توضیح دقیق و مثال های گرافیکی، به محدوده واقعی و تفاوت آن با محدوده مراجعه کنید.

از آنجایی که ورودی‌ها شامل بسته قبلی می‌شوند، نمی‌توانیم محدوده واقعی را برای ردیف اول داده‌هایمان محاسبه کنیم، بنابراین با ردیف دوم (ردیف 5 در صفحه‌گسترده) شروع می‌کنیم. بیایید محدوده واقعی را در ستون F قرار دهیم. فرمول در سلول F5 این است:

. که در آن C5 نوار فعلی بالا، D5 نوار فعلی پایین، و E4 نوار بسته قبلی است. MAX تابع اکسل است که حداکثر آرگومان های داده شده را برمی گرداند.

اطمینان حاصل کنید که از ارجاعات نسبی استفاده می کنید (بدون علامت دلار) و فرمول را از ردیف 6 به پایین در ردیف های دیگر کپی کنید.

ما محدوده واقعی را برای هر نوار محاسبه کرده ایم. اکنون می توانیم ATR را به عنوان میانگین متحرک محدوده واقعی محاسبه کنیم.

محاسبه ATR: سه روش

سه روش هموار سازی محبوب برای محاسبه ATR از محدوده واقعی وجود دارد. روش متوسط حرکت ساده ساده ترین این سه است ، بنابراین ما با آن یکی شروع خواهیم کرد. سپس ما دو روش دیگر را نیز محاسبه خواهیم کرد؟میانگین متحرک نمایی و روش هموار سازی وایلدر ، که روش اصلی استفاده شده توسط J. Welles Wilder ، مخترع ATR است.

متوسط حرکت ساده ATR

روش SMA بسیار ساده است. در اکسل شما با استفاده از میانگین عملکرد اکسل ، ATR را به عنوان میانگین حسابی از محدوده واقعی در تعداد مشخصی از آخرین میله ها محاسبه می کنید."تعداد خاصی" از میله ها دوره ATR نامیده می شود و تنها پارامتر این شاخص است.

در این آموزش از 21 به عنوان دوره ATR استفاده خواهم کرد. من در حال کار با داده های روزانه و 21 روز معاملاتی هستم که تقریباً یک ماه است. هیچ دوره "صحیح" وجود ندارد و تنظیمات مختلف متناسب با اهداف مختلف ، بازارهای مختلف و سبک های مختلف تجارت است.

بیایید تنظیم دوره را در سلول G2 قرار دهیم.

در ستون G میانگین ستون F (محدوده واقعی) را محاسبه خواهیم کرد. ما محاسبات خود را پویا خواهیم کرد ، که امکان تغییر دوره ATR را به راحتی با تغییر مقدار در سلول G2 ، بدون نیاز به بازنویسی هر فرمول ، امکان پذیر خواهد کرد.

ما می توانیم این کار را با ترکیب میانگین با یک عملکرد اکسل دیگر انجام دهیم؟انحراف. بیایید در ردیف آخر داده های خود شروع کنیم. فرمول موجود در سلول G68:

ترکیب متوسط افست توضیح داده شده است

کل عملکرد افست در یک عملکرد متوسط به عنوان آرگومان آن است. افست طیف وسیعی از سلول ها را برمی گرداند و میانگین آن میانگین حسابی آن سلول ها را محاسبه می کند.

Offset خود پنج آرگومان دارد که دامنه سلول ها را برای بازگشت مشخص می کند. بیایید هرکدام را توضیح دهیم.

اولین استدلال ، "مرجع" ، جایی است که می خواهید به دنبال دامنه باشید. در مثال ما این سلول F68 است که دامنه واقعی نوار فعلی است.

دو استدلال بعدی ، "ردیف" و "Cols" ، تصمیم می گیرند که چند ردیف به سمت بالا یا پایین و چند ستون سمت چپ یا راست برای تغییر مکان برای یافتن نقطه شروع دامنه. در مورد ما ، ما می خواهیم این محدوده همیشه شامل آخرین ردیف های N در ستون F باشد ، که با F68 پایان می یابد ، جایی که N دوره ATR در سلول G2 است. با تنظیم دوره فعلی 21 ، ما به استدلال "ردیف ها" از افست نیاز داریم ت ا-20 باشد تا 20 ردیف از سلول F68 به سلول F48 برگردد. ATR به طور متوسط 21 سلول از F48 تا F68 محاسبه می شود. استدلال "Cols" صفر است ، زیرا ما می خواهیم در ستون F.

دو استدلال آخر ، "ارتفاع" و "عرض" ، اندازه دامنه را تصمیم می گیرند. در مورد ما می خواهیم "ارتفاع" برابر با دوره ATR و "عرض" برابر با 1 باشد.

شما می توانید با تنظیم یک دوره ATR کوچک مانند 2 یا 3 ، فرمول را آزمایش کنید و بررسی کنید که میانگین حسابی سلولهای مورد نظر شما را برمی گرداند. نتیجه داده های روزانه GLD در 31 آگوست 2017 با دوره ATR 21 1. 111905 است. این میانگین دامنه واقعی GLD در 21 روز معاملاتی منتهی به 31 اوت است.

می توانید فرمول را در ردیف های دیگر کپی کنید تا یک سری زمانی از ATR دریافت کنید.

رفع #REF! خطاها

در چند ردیف اول می بینید که محاسبه ATR ما #REF برمی گردد! خطااین امر به این دلیل است که این فرمول در تلاش است تا میانگین دامنه ای را که قبل از ردیف 1 شروع می شود محاسبه کند. به عنوان مثال ، شما نمی توانید 20 ردیف از ردیف 5 بروید.

اگر نمی خواهید این خطاها را ببینید و سلول های خالی را ترجیح دهید ، فرمول را در سلول G5 به شرح زیر تنظیم کنید:

این یک عملکرد IF است که بررسی می کند که شماره ردیف به اندازه کافی زیاد است تا ATR محاسبه شود. اگر باشد ، فرمول ATR را محاسبه می کند. اگر اینگونه نباشد ، فرمول "" باز می گردد. شرط این است:

اگر تعداد ردیف از یا برابر 4 به علاوه دوره ATR باشد ، ATR را می توان محاسبه کرد. به عنوان مثال ، اگر دوره ATR را به 2 تنظیم کنید ، ردیف اول که محاسبه ATR امکان پذیر است ، ردیف 6 است ، زیرا ردیف اول داده 4 است ، ردیف اول با محدوده واقعی 5 است و حداقل به دو ردیف نیاز داریددامنه واقعی ، که 5 و 6 است.

توجه داشته باشید که این فرمول فقط زمانی کار می کند که ردیف اول داده ردیف 4 باشد. اگر ردیف ها را وارد یا حذف کنید و داده های شما در یک ردیف دیگر شروع می شود ، باید این فرمول را تنظیم کنید. روش های مختلفی برای ساخت این پویا نیز وجود دارد؟من این را به شما واگذار می کنم.

اولین ردیف های صفحه گسترده ما اکنون به این شکل است:

روش نمایی و وایلدر

ما با استفاده از روش متوسط حرکت ساده ، میانگین محدوده واقعی را با موفقیت محاسبه کرده ایم. برای دو روش دیگر ، به قسمت دوم ادامه دهید.