استخدام إكسل لتتبع محفظتك الاستثمارية
في هذه المقالة، سننظر في كيفية استخدام إكسل لتتبع أداء حسابك. هذه بمثابة دليل أساسي للأشخاص الذين لديهم خبرة قليلة أو معدومة في إكسل.
استخدام إكسل لتتبع محفظتك الاستثمارية – الحصول على بعض البيانات
قبل أن نتمكن من القيام بأي شيء باستخدام إكسل، نحتاج إلى الحصول على بعض الأرقام! المعلومات التي تستخدمها في إكسل تُسمى “بيانات”. بعض منها سنحتاج إلى كتابته، وبعض يمكن نسخه ولصقه، وبعض يمكننا تنزيله مباشرة كملف إكسل.
الحصول على قيم محفظتك التاريخية (كتابة الأرقام في جدول البيانات)
يمكنك العثور على قيم محفظتك التاريخية في صفحة لوحة التحكم الخاصة بك، مباشرة فوق مخطط قيمة محفظتك:
سيقوم هذا بتنزيل جدول بيانات بقيم محفظتك، وعدد الصفقات، ورقمك لكل يوم كنت فيه في المسابقة الحالية.
الحصول على الأسعار التاريخية للأسهم (نسخ ولصق البيانات في جدول البيانات)
في هذا المثال، نريد الحصول على الأسعار التاريخية لسهم حتى نتمكن من النظر في كيفية تحرك السعر بمرور الوقت. أولاً، افتح جدول بيانات فارغ جديد في إكسل.
سنستخدم سهم Sprint (الرمز: S). انتقل إلى صفحة الاقتباسات وابحث عن S:
بعد ذلك، انقر على علامة التبويب “التاريخية” في أعلى يمين الاقتباس:
بعد ذلك، قم بتغيير تواريخ “البداية” و”النهاية” إلى الوقت الذي تريد النظر فيه. في هذا المثال، سنستخدم نفس التواريخ التي حفظناها لقيم محفظتنا، من 11 يناير إلى 15 يناير 2016.
بمجرد تحميل الأسعار التاريخية، قم بتظليل كل شيء من “التاريخ” إلى آخر رقم تحت “الإغلاق المعدل” (يجب أن يبدو هكذا):
الآن انسخ البيانات، وحدد الخلية A1 في جدول بيانات إكسل الفارغ الخاص بك، والصقها.
تهانينا، لقد قمنا الآن باستيراد بعض البيانات إلى إكسل! لاحظ أن عناوين الأعمدة الخاصة بك قد تم اكتشافها بالفعل – سيكون هذا مهمًا لاحقًا.
من هناك، هناك بعض الأشياء التي نود تغييرها.
تغيير ترتيب بياناتك
أولاً، هذه البيانات في ترتيب عكسي مقارنة بقيم محفظتنا. للحصول عليها بنفس الترتيب، نريد فرز هذه الجدول حسب التاريخ، من الأقدم إلى الأحدث. في القائمة العلوية، انقر على “البيانات“، ثم انقر على “فرز“:
يمكنك الآن اختيار ما نريد فرزه، وكيفية فرزه. إذا نقرت على القائمة المنسدلة تحت “فرز حسب”، سيقوم إكسل بإدراج جميع عناوين الأعمدة التي يكتشفها (اختر “التاريخ“). بعد ذلك، تحت “الترتيب”، نريد “الأقدم إلى الأحدث“:
الآن يجب أن تكون بياناتك بنفس ترتيب قيم محفظتك من قبل.
تغيير عرض العمود
بعد ذلك، ستلاحظ أن “الحجم” يظهر فقط كـ “########”. هذا ليس بسبب وجود خطأ، الرقم كبير جدًا ليتمكن من التناسب مع عرض الخلية لدينا. لإصلاح ذلك، يمكننا زيادة أو تقليل عرض خلايانا عن طريق سحب الحدود بين الصفوف والأعمدة:
نصيحة: إذا نقرت نقرة مزدوجة على هذه الحدود، ستقوم الخلية الموجودة على اليسار بضبط عرضها تلقائيًا لتناسب البيانات الموجودة فيها.
إذا كنت تريد ضبط جميع خلاياك تلقائيًا مرة واحدة، في القائمة العلوية انقر على “تنسيق”، و”تعديل عرض العمود تلقائيًا”:
بمجرد أن تقوم بضبط عمود الحجم الخاص بك، يجب أن يكون كل شيء مرئيًا!
إزالة الأعمدة التي لا تحتاجها
أعتقد أننا سنرغب فقط في استخدام سعر الإغلاق المعدل في الحسابات التي سنقوم بها لاحقًا (سعر “الإغلاق المعدل” هو سعر الإغلاق المعدل لأي تقسيمات أو توزيعات أرباح حدثت منذ ذلك اليوم). هذا يعني أنني أريد الاحتفاظ بعمود “التاريخ” و”الإغلاق المعدل”، ولكن حذف الباقي.
إذا حاولت فقط تحديد البيانات وحذفها، ستنتهي بمساحة فارغة كبيرة:
بدلاً من ذلك، انقر على “B” واسحب حتى “H” لتحديد الأعمدة الكاملة:
الآن انقر بزر الماوس الأيمن واضغط على “حذف”، وستختفي الصفوف بالكامل. الآن سيكون سعر الإغلاق المعدل هو عمودك الجديد B، دون أي فراغات. لديك الآن بيانات أسعارك التاريخية، لذا احفظ هذا الملف excel حتى نتمكن من العودة إليه لاحقًا.
الحصول على تاريخ معاملتك والمراكز المفتوحة
مثل قيمة محفظتك التاريخية، نجعل هذا سهلاً – بجوار مراكزك المفتوحة وتاريخ معاملتك، ستجد المزيد من أزرار تصدير Excel:
بغض النظر عن نطاق التاريخ المعروض في صفحة تاريخ المعاملات، سيقوم التصدير بسحب تاريخ معاملتك بالكامل لهذه المسابقة.
استخدام Excel لتتبع محفظتك من الأسهم – الرسم البياني
الآن بعد أن لدينا بعض البيانات، دعنا نصنع بعض الرسوم البيانية بها! سنستعرض كيفية صنع الرسوم البيانية الخطية لقيمة محفظتك اليومية وتغير نسبة محفظتك، بالإضافة إلى مخطط عمودي يوضح مراكزك المفتوحة. هذه عادةً هي الجزء الأكثر متعة في استخدام Excel لتتبع محفظتك من الأسهم.
الرسم البياني الخطي – قيمة محفظتك اليومية
أولاً، نريد إنشاء رسم بياني خطي يوضح قيمة محفظتنا اليومية. أولاً، افتح جدول البيانات الذي يحتوي على قيم محفظتك اليومية:
بعد ذلك، قم بتظليل بياناتك، واضغط على “إدراج” في الشريط العلوي:
هنا، تحت قسم “المخططات“، انقر على المخطط الذي يحتوي على خطوط، واختر أول “مخطط خطي ثنائي الأبعاد“:
وهذا كل شيء! الرسم البياني الجديد جاهز للعرض. يمكنك حتى نسخ الرسم البياني ولصقه في Microsoft Word ليكون جزءًا من مستند، أو لصقه في محرر صور لحفظه كصورة.
الرسم البياني الخطي – تغيرات نسبة المحفظة
بعد ذلك، نريد إنشاء رسم بياني يوضح مقدار تغير محفظتنا كل يوم. للقيام بذلك، أولاً نحتاج إلى حساب ذلك فعليًا.
إجراء الحسابات في Excel
في العمود التالي، سنحسب تغير نسبة محفظتنا اليومية. أولاً، في العمود التالي، أضف العنوان “% تغيير”
الآن نحتاج إلى إجراء حسابنا. لحساب تغير النسبة كل يوم، نريد أن نأخذ الفرق بين قيمة اليوم الأكثر حداثة ناقصًا قيمة اليوم السابق، ثم نقسم ذلك على قيمة اليوم السابق:
تغير النسبة = (قيمة اليوم 2 – قيمة اليوم 1) / قيمة اليوم 1
للقيام بذلك، في الخلية C3 يمكننا إجراء بعض العمليات لإجراء الحساب لتغير النسبة. لكتابة صيغة، ابدأ بكتابة “=”. يمكنك استخدام نفس الرموز التي تستخدمها عند الكتابة على الورق لكتابة صيغك، ولكن بدلاً من كتابة كل رقم، يمكنك فقط تحديد الخلايا.
لحساب تغير النسبة الذي رأيناه بين اليوم 1 واليوم 2، استخدم الصيغة أعلاه في الخلية C3. يجب أن تبدو هكذا:
الآن انقر على الزاوية السفلية اليمنى من تلك الخلية واسحبها إلى آخر صف يحتوي على بيانات، سيقوم Excel تلقائيًا بنسخ الصيغة لكل خلية:
لديك الآن نسبك! إذا كنت تريد عرضها كنسب مئوية بدلاً من أرقام كاملة، انقر على “C” لتحديد العمود بالكامل، ثم انقر على علامة النسبة الصغيرة في الأدوات في أعلى الصفحة:
إنشاء الرسم البياني الخاص بك باستخدام أعمدة معينة فقط
الآن نريد إنشاء رسم بياني يوضح كيف كانت محفظتنا تتغير كل يوم، ولكن إذا حاولنا القيام بنفس الشيء كما في السابق (تحديد جميع البيانات وإدراج “مخطط خطي”، فإن الرسم البياني لا يخبرنا بالكثير:
هذا لأن الرسم البياني يحاول إظهار كل من القيمة الإجمالية للمحفظة وتغير النسبة في نفس الوقت، لكنهما على مقياس مختلف تمامًا!
لتصحيح ذلك، نحتاج إلى تغيير البيانات المعروضة. انقر بزر الماوس الأيمن على الرسم البياني الخاص بك واضغط على “تحديد البيانات”:
هذه هي الطريقة التي نحدد بها البيانات المعروضة في الرسم البياني. العناصر على الجانب الأيسر ستشكل خطوطنا، والعناصر على اليمين ستشكل العناصر التي تظهر على المحور X (في هذه الحالة، تواريخنا).
قم بإلغاء تحديد “قيمة المحفظة”، ثم انقر فوق “موافق” لتحديث الرسم البياني الخاص بك:
هذا أقرب، لكننا نريد الآن إعادة توجيه التواريخ إلى أسفل الرسم البياني (هنا هي على طول نقطة “0” من محور Y).
للقيام بذلك، انقر بزر الماوس الأيمن على التواريخ واختر “تنسيق المحور”:
ستظهر قائمة جديدة على الجانب الأيمن من الشاشة. هنا، انقر على “التسميات”، ثم قم بتعيين موضع التسمية إلى “منخفض”.
تهانينا، لقد اكتمل الرسم البياني الخاص بك الآن! يمكنك الآن رؤية الأيام التي كانت فيها محفظتك جيدة، والأيام التي تكبدت فيها خسائرك.
مخطط الأعمدة – رؤية مراكزك المفتوحة
بعد ذلك، نود إنشاء مخطط أعمدة يوضح مقدار مراكزنا المفتوحة الحالية في كل سهم، أو ETF، أو صندوق مشترك.
أولاً، افتح جدول البيانات الخاص بك مع مراكزك المفتوحة. يجب أن يبدو شيئًا مثل هذا:
نظرًا لأننا نريد إنشاء مخطط أعمدة، يمكن أن يكون لدينا عمودان فقط من البيانات. نريد عمودًا يظهر الرمز، وعمودًا ثانيًا يظهر قيمته. عمود “التكلفة الإجمالية” هو القيمة السوقية الحالية لهذه الأسهم، لذا هذا هو العمود الذي نريد الاحتفاظ به. ومع ذلك، لا نريد حذف الكمية والسعر، لأننا قد نحتاج إليهما لاحقًا. بدلاً من ذلك، حدد الأعمدة التي لا تريدها، وانقر بزر الماوس الأيمن على حرفها (A و C في هذه الحالة). ثم، اختر “إخفاء”:
الآن الأعمدة التي لا نريدها في مخططنا مخفية. يمكننا دائمًا استعادتها لاحقًا بالذهاب إلى “تنسيق” -> “الرؤية” -> “إظهار الأعمدة”. الآن حدد بياناتك وأدخل “مخطط أعمدة” بدلاً من “مخطط خطي”:
قبل أن تنتهي، سيقول مخططك “التكلفة الإجمالية”. يمكنك تغيير ذلك بالنقر على “التكلفة الإجمالية” وتحريرها لتقول ما تريده (مثل “توزيع المحفظة”):
هذا الرسم البياني الآن مكتمل، ولكن يمكنك أيضًا تجربة تغيير نوع المخطط للحصول على مخطط دائري. أولاً، انقر بزر الماوس الأيمن على الرسم البياني الخاص بك واختر “تغيير نوع المخطط”:
بعد ذلك، ابحث عن مخططات “الدائرة”، واختر أي مخطط تفضله.
أخيرًا، الآن لا نعرف أي قطعة من الدائرة تمثل أي سهم. لإضافة هذه المعلومات، انقر على مخطط الدائرة الخاص بك، ثم في أعلى الصفحة انقر على “تصميم”. ثم اختر أي من الخيارات لتغيير شكل مخطط الدائرة الخاص بك.
تهانينا، لقد قمت بتحويل مخطط الأعمدة الخاص بك إلى مخطط دائري! يجب أن يبدو هذا مشابهًا تقريبًا لما لديك على الجانب الأيمن من صفحة مراكزك المفتوحة.
استخدام Excel لتتبع محفظة الأسهم الخاصة بك – حساب الربح والخسارة من تداولاتك
السبب الأكثر أهمية الذي يجعلك ترغب في استخدام Excel لتتبع محفظة الأسهم الخاصة بك هو محاولة حساب الربح والخسارة من كل صفقة. للقيام بذلك، افتح جدول البيانات الخاص بك مع تاريخ المعاملات. يجب أن يبدو شيئًا مثل هذا:
نصيحة: إذا لم تقم بشراء ثم بيع سهم، فلا يمكنك حساب مقدار الربح الذي حققته من الصفقة.
أولاً، نريد تغيير كيفية فرز البيانات حتى نتمكن من تجميع جميع الصفقات لنفس الرمز معًا. استخدم أداة “الفرز” للفرز أولاً حسب “الرمز”، ثم حسب “التاريخ” (من الأقدم إلى الأحدث).
بالنسبة لـ DWTI و SPY، لم نقم أبدًا “بإغلاق” مراكزنا (بيع سهم اشتريته، أو تغطية سهم قصير)، لذا لا يمكننا حساب الربح أو الخسارة. في الوقت الحالي، قم بإخفاء تلك الصفوف.
الآن نحن مستعدون للحساب! لنبدأ بالصفقة لـ S. هذه سهلة لأن الأسهم التي بعتها تساوي الأسهم التي اشتريتها. هذا يعني أنه إذا أضفنا فقط “المبلغ الإجمالي”، فسوف يخبرنا بالضبط بالربح أو الخسارة التي حققناها من الصفقة.
هذا لا يعمل مع UWTI، لأنني بعت عددًا مختلفًا من الأسهم عما اشتريته. هذا يعني أنني بحاجة أولاً إلى حساب التكلفة الإجمالية للأسهم التي بعتها، ثم يمكنني استخدام ذلك لتحديد أرباحي.
أولاً: اضرب سعر الشراء في عدد الأسهم التي بعتها:
ثانيًا: أضف هذا الرقم إلى “المبلغ الإجمالي” من عندما بعت أسهمك.
الآن لديك أرباحك أو خسائرك من هذه الصفقة. ملاحظة: هذه هي الطريقة إذا كنت قد اشتريت المزيد من الأسهم مما بعت – إذا اشتريت أسهمًا بأسعار مختلفة، ثم بعتها لاحقًا، ستحتاج إلى حساب متوسط التكلفة لاستخدامه في حسابك.