Hey guys! Are you looking to get a better handle on your investments in the Philippine Stock Exchange (PSE)? Well, you're in luck! Tracking your financials doesn't have to be a headache. In this article, we'll dive deep into how you can create and use a free PSE financials tracker using Excel. Yes, you heard that right – free! So, buckle up, and let's get started!

Why Use Excel for Tracking PSE Financials?

So, why should you even bother using Excel when there are tons of fancy apps and platforms out there? Good question! Let's break it down:

• Cost-Effective: The most obvious reason is that it's free! If you already have Microsoft Excel installed on your computer, you don't need to shell out any extra cash for expensive software subscriptions. This is especially great if you're just starting out and want to keep your costs low.
• Customizable: Excel is incredibly versatile. You can tailor your tracker to fit your specific needs and preferences. Want to track specific metrics? No problem! Want to create custom charts and graphs? Easy peasy! With Excel, you have the power to design your tracker exactly the way you want it.
• Offline Access: Unlike web-based platforms, Excel allows you to access your data offline. This is super handy when you're traveling or in areas with unreliable internet connections. You can update your tracker anytime, anywhere, without worrying about connectivity issues.
• Data Ownership: When you use Excel, you own your data. You don't have to worry about third-party companies having access to your financial information. This gives you greater control and peace of mind.
• Familiarity: Let's face it, most of us are already familiar with Excel to some extent. This means you won't have to spend hours learning a new software program. You can start building your tracker right away, using skills you already have.

Setting Up Your Excel Tracker

Okay, now that we've established why Excel is a great choice, let's get down to the nitty-gritty of setting up your tracker. Here's a step-by-step guide to get you started:

1. Open a New Excel Workbook:

   • The first thing you'll want to do is open up a new Excel workbook. Just launch Excel and select "Blank Workbook" to start fresh.

2. Define Your Columns:

   • Think about the key information you want to track for each stock. Here are some essential columns to include:
     • Date: The date of the transaction.
     • Stock Symbol: The ticker symbol of the stock (e.g., TEL for PLDT).
     • Company Name: The full name of the company.
     • Transaction Type: Whether you bought or sold the stock.
     • Quantity: The number of shares bought or sold.
     • Price per Share: The price you paid or received for each share.
     • Brokerage Fees: Any fees charged by your broker.
     • Other Fees: Any other fees associated with the transaction (e.g., SCCP fees, VAT).

3. Create Headers:

   • In the first row of your worksheet, enter the column headers you defined in the previous step. Make sure to format them in a way that's easy to read (e.g., bold, different font).

4. Input Your Data:

   • Now comes the fun part – entering your historical transaction data. Go through your brokerage statements and input each transaction into your Excel sheet. Be as accurate as possible to ensure your tracker is reliable.

5. Add Formulas:

   • This is where Excel really shines. You can use formulas to automate calculations and gain valuable insights. Here are some essential formulas to include:
     • Total Cost/Proceeds: =(Quantity * Price per Share) + Brokerage Fees + Other Fees (for purchases)
     • Total Proceeds: =(Quantity * Price per Share) - Brokerage Fees - Other Fees (for sales)
     • Average Cost per Share: =SUM(Total Cost of all Purchases of Stock X) / SUM(Quantity of all Purchases of Stock X)
     • Current Value: =Quantity * Current Market Price (you'll need to manually update the current market price).
     • Profit/Loss: =Current Value - Total Cost

6. Format Your Data:

   | Read Also : Get A DC Library Card Online: Easy Guide
   • Make your tracker visually appealing and easy to understand by formatting your data. Use number formatting to display currency values correctly, and use conditional formatting to highlight key metrics (e.g., profitable trades in green, losing trades in red).

7. Create Charts and Graphs:

   • Visualize your data by creating charts and graphs. Excel offers a wide range of chart types to choose from. Some useful charts to include are:
     • Portfolio Allocation: A pie chart showing the percentage of your portfolio allocated to each stock.
     • Profit/Loss Over Time: A line chart showing your profit or loss over time.
     • Transaction History: A bar chart showing your buy and sell transactions for each stock.

Advanced Features to Enhance Your Tracker

Want to take your Excel tracker to the next level? Here are some advanced features to consider:

• Data Validation: Use data validation to ensure data accuracy. For example, you can create a dropdown list of stock symbols to prevent typos.
• Pivot Tables: Use pivot tables to summarize and analyze your data in different ways. For example, you can create a pivot table to see your total profit/loss for each stock.
• Macros: Automate repetitive tasks by creating macros. For example, you can create a macro to automatically update the current market price of your stocks.
• External Data Connections: Connect your Excel tracker to external data sources to automatically update stock prices and other financial data. This requires some technical knowledge, but it can save you a lot of time and effort.

Tips for Maintaining Your Tracker

Creating your Excel tracker is just the first step. To keep it useful, you need to maintain it regularly. Here are some tips to keep in mind:

• Update Regularly: Make it a habit to update your tracker regularly, ideally after each transaction. This will ensure that your data is always accurate and up-to-date.
• Back Up Your Data: Back up your Excel file regularly to prevent data loss. You can save it to a cloud storage service like Google Drive or Dropbox, or to an external hard drive.
• Review Your Data: Take some time each month to review your data and analyze your investment performance. This will help you identify trends and make informed investment decisions.
• Customize as Needed: Don't be afraid to customize your tracker as your needs change. Add new columns, formulas, or charts as needed to stay on top of your investments.

Example Scenario: Tracking Your First Stock

Let's say you bought 100 shares of PLDT (TEL) at PHP 1,200 per share on January 1, 2024. Your brokerage fees were PHP 50. Here's how you would enter this information into your Excel tracker:

Now, let's say you sold 50 shares of TEL at PHP 1,250 per share on February 1, 2024. Your brokerage fees were again PHP 50. Here's how you would enter this information:

By consistently tracking your transactions, you can easily monitor your investment performance and make informed decisions.

Common Mistakes to Avoid

While using Excel is straightforward, here are some common mistakes to avoid:

• Inaccurate Data Entry: Always double-check your data to ensure accuracy. Even small errors can throw off your calculations.
• Not Backing Up Your Data: Regularly back up your Excel file to prevent data loss.
• Ignoring Fees: Don't forget to include brokerage fees and other transaction costs in your calculations.
• Not Updating Regularly: Make it a habit to update your tracker regularly to keep your data current.
• Overcomplicating Things: Keep your tracker simple and easy to understand. Don't add unnecessary complexity.

Alternatives to Excel

While Excel is a great option, it's not the only game in town. Here are some alternative tools you might want to consider:

• Google Sheets: A free, web-based spreadsheet program that's similar to Excel. It's great for collaboration and accessing your data from anywhere.
• Investment Tracking Apps: There are many investment tracking apps available for smartphones and tablets. These apps often offer features like automatic price updates and portfolio analysis.
• Brokerage Platforms: Some brokerage platforms offer built-in portfolio tracking tools. These tools can be convenient, but they may not be as customizable as Excel.

Final Thoughts

So, there you have it! Creating a free PSE financials tracker in Excel is a great way to take control of your investments and gain valuable insights into your portfolio performance. It might sound a bit intimidating at first, but once you get the hang of it, you'll wonder how you ever managed without it. Remember to keep your tracker updated, back up your data, and don't be afraid to customize it to fit your specific needs. Happy tracking, and happy investing!