এক্সেলে এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করুন: ধাপে ধাপ স্পষ্ট গাইড

এক্সপোনেনশিয়াল মুভিং এভারেজ (EMA) ভবিষ্যতের ডেটা পূর্বাভাস দেওয়া এবং স্টক প্রাইস মুভমেন্ট স্মুথ করার জন্য ব্যাপকভাবে ব্যবহৃত হয়। যদি আপনি জানতে চান এক্সপোনেনশিয়াল মুভিং এভারেজ কী এবং এক্সেলে এটিকে কীভাবে ক্যালকুলেট করতে হয়, তবে আপনি সঠিক জায়গায় আছেন। এই আর্টিকেলে আমি এক্সেলে এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করার ৩টি উপযুক্ত পদ্ধতি দেখাব।

এক্সপোনেনশিয়াল মুভিং এভারেজ (EMA)

এক্সপোনেনশিয়াল মুভিং এভারেজ (EMA)-কে এক্সপোনেনশিয়ালি ওয়েইটেড মুভিং এভারেজ ও বলা হয়। এটি মুভিং এভারেজ-এর একটি ধরন যা সিম্পল মুভিং এভারেজের চেয়ে সাম্প্রতিক ডেটাকে অধিক ওয়েইটেজ দেয়। তাই এক্সপোনেনশিয়াল মুভিং এভারেজের ক্ষেত্রে স্মুথিং সিম্পল মুভিং এভারেজের চেয়ে বেশি। সময়কাল বড় হলে সাম্প্রতিক ডেটার গুরুত্ব কমে যায়। EMA ক্যালকুলেট করার ফর্মুলা হলো:

EMA_t= α x Y_t-1+ (1-α) x EMA_t-1

যেখানে,

EMA_t = বর্তমান পিরিয়ডের এক্সপোনেনশিয়াল মুভিং এভারেজ
α = ס্মুথিং মাল্টিপ্লায়ার। α ০ থেকে ১ এর মধ্যে যেকোনো সংখ্যা হতে পারে। α-এর মান যত বেশি, অতীতের ডেটাকে তত বেশি ওয়েইটেজ দেওয়া হয়। α-এর আদর্শ রেঞ্জ ০.১ থেকে ০.৩ পর্যন্ত। α ক্যালকুলেট করার আরেকটি ফর্মুলা: α = 2/(n+1) ؛ এখানে n হলো পিরিয়ডের সংখ্যা।

ধরুন, আমাদের নিচের ডেটাসেট আছে যেখানে ২০২০ সালের ১২ মাসের সেলস ডেটা রয়েছে। এখন আমরা এক্সপোনেনশিয়াল মুভিং এভারেজ ব্যবহার করে জানুয়ারি ২০২১-এর সেলস ডেটা পূর্বাভাস দিতে চাই।

এক্সেলে এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করুন: ধাপে ধাপ স্পষ্ট গাইড

এক্সেলে এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করার ৩টি পদ্ধতি

১. ডেটা অ্যানালিসিস টুলপ্যাক দিয়ে এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করা

ডেটা অ্যানালিসিস টুলপ্যাক ব্যবহার করে এক্সপোনেনশিয়াল মুভিং এভারেজ প্রয়োগ করে ভবিষ্যতের ডেটা পূর্বাভাস দেওয়া যায়। তার জন্য প্রথমে আপনার এক্সেলে এই টুলপ্যাক অ্যাড করতে হবে।

File ট্যাবে যান এবং Options সিলেক্ট করুন।

Excel Options উইন্ডো খুলবে। এখন Add-ins ট্যাবে যান এবং Go ক্লিক করুন।

Add-ins বক্স খুলবে। Analysis ToolPak-এ চেকমার্ক দিয়ে OK ক্লিক করুন।

এখন Data ট্যাবে যান এবং Data Analysis ক্লিক করুন।

Data Analysis উইন্ডো খুলবে। Exponential Smoothing সিলেক্ট করে OK ক্লিক করুন।

এখন Exponential Smoothing উইন্ডোতে:

Input Range বক্সে সেলস ডেটা ওয়ালা সেলগুলো সিলেক্ট করুন (প্রtyjাস পিরিয়ডের খালি সেলও সহ)। Damping factor বক্সে α-এর মান দিন। এই উদাহরণে আমরা α = 0.3 নিচ্ছি।
তারপর Output Range সিলেক্ট করুন (যেখানে আপনি EMA ক্যালকুলেশন চান)।
যদি আপনি EMA এবং একচুয়াল ডেটার গ্রাফিক্যাল রিপ্রেজেন্টেশন দেখতে চান, তবে Chart Output বক্সে চেকমার্ক দিন।
শেষে OK ক্লিক করুন।

ফলস্বরূপ, এক্সেল আপনার সিলেক্ট করা সেলস রেঞ্জে EMA দিয়ে দেবে এবং জানুয়ারি ২০২১-এর সেলস পূর্বাভাস পাবেন।

যদি আপনি লক্ষ্য করেন, ফেব্রুয়ারির EMA জানুয়ারির সেলসের সমান। এক্সেল Analysis ToolPak সরলভাবে ধরে নেয় যে প্রথম EMA গত পিরিয়ডের ডেটার সমান, যা ফোরকাস্টিং-এ নেইভ মেথড-ও বলা হয়।

এক্সেল একচুয়াল ডেটা এবং ক্যালকুলেটেড EMA ফোরকাস্টের তুলনামূলক চার্টও দেখাবে।

২. এক্সেলে ম্যানুয়ালি এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করা

Analysis ToolPak ব্যবহার না করে আমরা ম্যানুয়ালি EMA ক্যালকুলেশন ফর্মুলা ইনসার্ট করে এক্সপোনেনশিয়াল মুভিং এভারেজ বের করতে পারি। সব পিরিয়ডের জন্য EMA ক্যালকুলেট করতে হলে প্রথম পিরিয়ডের EMA জানতে হবে। এটি নেইভ, সিম্পল মুভিং এভারেজ, ৩ পিরিয়ড মুভিং এভারেজ ইত্যাদি পদ্ধতিতে বের করা যায়। এই উদাহরণে আমরা ৩ পিরিয়ড মুভিং এভারেজ মেথড (3MA) ব্যবহার করব।

৩ পিরিয়ড মুভিং এভারেজ ক্যালকুলেট করতে:

এপ্রিল মাসের সারি (সেল E8)-এ নিচের ফর্মুলা টাইপ করুন:

=(D5+D6+D7)/3

ফর্মুলাটি গত তিন বছরের সেলসের গড় রিটার্ন করবে। তারপর সেল E8 ড্র্যাগ করে সব পিরিয়ডের 3MA পান।

এখন EMA ক্যালকুলেট করার জন্য α দরকার। এবার নিচের ফর্মুলা দিয়ে α নির্ধারণ করব:

=2/(12+1)

আমরা n=12 নিয়েছি কারণ আমাদের ডেটাসেটে ১২টি পিরিয়ড রয়েছে।

পরিবর্তে, চতুর্থ পিরিয়ডের EMA নির্ধারণের জন্য নিচের ফর্মুলা ইনসার্ট করুন:

=(1-$C$3)*D7+$C$3*E8

এই ফর্মুলা প্রথম পিরিয়ডের EMA দেবে যেখানে আমরা এই পিরিয়ডের 3MA-কে গত পিরিয়ডের EMA হিসেবে ব্যবহার করেছি।

এখন প্রথম বছরের EMA পেয়ে গেলে, সব পিরিয়ডের EMA সহজে ক্যালকুলেট করা যাবে।

সেল F9-এ নিচের ফর্মুলা টাইপ করুন:

=(1-$C$3)*D8+$C$3*F8

এখানে, $C$3 এ α-এর মান, D8 গত পিরিয়ডের সেলস এবং F8 গত পিরিয়ডের EMA।

শেষে, সেল E9 ডেটাসেটের শেষ পর্যন্ত ড্র্যাগ করুন।

ফলস্বরূপ, আপনি ফোরকাস্ট পিরিয়ড (জানুয়ারি ২০২১) সহ সব পিরিয়ডের EMA পাবেন।

আরও পড়ুন: এক্সেল চার্টে মুভিং এভারেজ জেনারেট করার ৪ পদ্ধতি

৩. VBA ব্যবহার করে EMA

স্টক প্রাইস মুভমেন্ট স্মুথ করার জন্যও এক্সপোনেনশিয়াল মুভিং এভারেজ ব্যবহৃত হয়। এই পদ্ধতিতে, আমরা দেখব কিভাবে মাইক্রোসফট ভিজুয়াল বেসিক অ্যাপ্লিকেশন (VBA) দিয়ে কাস্টম ফাংশন তৈরি করে এক্সপোনেনশিয়াল মুভিং এভারেজ ক্যালকুলেট করা যায়।

স্টক প্রাইস স্মুথ করার সময় EMA ক্যালকুলেশন ফর্মুলায় গত পিরিয়ডের ডেটার বদলে বর্তমান পিরিয়ডের প্রাইস ব্যবহার করা হয়। তাই ফর্মুলা হয়:

EMA_t= α x P + (1-α) x EMA_t-1

এখানে, P হলো বর্তমান পিরিয়ডের প্রাইস।

ফর্মুলাটি এইভাবে ও লেখা যায়:

EMA_t = α x P + (1 – α) x EMA_t-1

ধরুন, আমাদের ডেটাসেটে একটি স্টকের বছরের বিভিন্ন মাসের গড় প্রাইস রয়েছে। এখন, আমরা VBA ব্যবহার করে কাস্টম ফর্মুলা তৈরি করে স্টক প্রাইসের এক্সপোনেনশিয়াল মুভিং এভারেজ বিভিন্ন মাসে ক্যালকুলেট করব।

প্রথমে শিটের নামের ওপর রাইট ক্লিক করে View Code সিলেক্ট করুন।

VBA Code উইন্ডো খুলবে। এখানে নিচের কোড টাইপ করুন:

Private Sub Workbook_Open() AddUDF End Sub

এই কোডটি একটি ম্যাক্রো তৈরি করবে যা এক্সেল ওয়ার্কবুকের এই শিটে কাস্টম কোড যোগ করবে।

এরপর VBA উইন্ডোর বাম প্যানেল থেকে শিটের নামের ওপর রাইট ক্লিক করে Insert > Module এ যান।

VBA Module উইন্ডো খুলবে। এখন নিচের কোড মডিউল উইন্ডোতে ইনসার্ট করুন:

Sub AddUDF() Application.MacroOptions macro:="EMA", _ Description:="Returns the Exponential Moving Average." & Chr(10) & Chr(10) & _ "Select last period's EMA or last period's price if current period is the first." & Chr(10) & Chr(10) & _ "Followed by current price and n." & Chr(10) & Chr(10) & Chr(10) & _ "The decay factor of the exponential moving average is calculated as alpha=2/(n+1)", _ Category:="Technical Indicators" End Sub Public Function EMA(EMAYesterday, price, n) alpha = 2 / (n + 1) EMA = alpha * price + (1 - alpha) * EMAYesterday End Function

এই কোডটি EMA (EMAYesterday, price, n) নামে একটি কাস্টম ফাংশন তৈরি করবে।