Want to keep a close eye on your investments in the Philippine Stock Exchange (PSE)? Or maybe you're just curious about how stocks are performing? Well, you're in luck! You can easily track stocks using a combination of PSE data, Yahoo Finance, and the power of Google Sheets. Let's dive into how you can set this up, step by step.

Why Use Google Sheets for Stock Tracking?

Before we get started, let's talk about why Google Sheets is such a great tool for this. First off, it's free! You don't need to buy any expensive software. Secondly, it's super flexible. You can customize it to show exactly the data you want. Thirdly, it's accessible anywhere. As long as you have an internet connection, you can check your stock portfolio from any device. Plus, you can easily share it with others if you want to collaborate or show off your investment skills.

Setting Up Your Google Sheet

Alright, let's get practical. Open up Google Sheets and create a new spreadsheet. Give it a descriptive name like "My Stock Portfolio Tracker" or something similar. Now, you'll want to set up the basic columns for your data. Here are some essential columns to include:

• Stock Symbol: This is the ticker symbol for the stock you want to track (e.g., TEL for PLDT, SMPH for SM Prime). Make sure to get this right! You can get it wrong even if you think you know. Always double-check!
• Company Name: The full name of the company. This helps you easily identify the stock.
• Current Price: The latest price of the stock.
• Change: The amount the price has changed since the previous day's close.
• % Change: The percentage change in price.
• Volume: The number of shares traded today.
• 52-Week High: The highest price the stock has reached in the past 52 weeks.
• 52-Week Low: The lowest price the stock has reached in the past 52 weeks.
• Your Shares: How many shares of that stock you own.
• Cost Basis: The average price you paid per share.
• Total Value: The current market value of your shares (Current Price * Your Shares).
• Gain/Loss: The difference between your total value and your cost basis.

Feel free to add more columns as you see fit! For example, you might want to include columns for dividend yield, P/E ratio, or any other financial metrics that are important to you. It is important to keep track and measure the dividends, because that is passive income.

Getting Stock Data with Google Finance Functions

Now for the fun part: getting the stock data into your spreadsheet! Google Sheets has a built-in function called GOOGLEFINANCE that can pull real-time (or near real-time) stock data from Google Finance. Here's how to use it.

Getting the Current Price

In the cell where you want to display the current price, enter the following formula:

=GOOGLEFINANCE(A2, "price")

Replace A2 with the cell containing the stock symbol. For example, if your stock symbol "TEL" is in cell A2, the formula will fetch the current price of PLDT stock. This is the first key step in managing your Google Sheet. Remember to replace the cell as needed!

Getting Other Stock Information

The GOOGLEFINANCE function can also retrieve other data, such as the change, % change, volume, 52-week high, and 52-week low. Here are some examples:

• Change: =GOOGLEFINANCE(A2, "change")
• % Change: =GOOGLEFINANCE(A2, "changepct")
• Volume: =GOOGLEFINANCE(A2, "volume")
• 52-Week High: =GOOGLEFINANCE(A2, "high52")
• 52-Week Low: =GOOGLEFINANCE(A2, "low52")

Just replace A2 with the appropriate cell containing the stock symbol. Copy and paste these formulas into the corresponding columns in your spreadsheet.

Calculating Your Portfolio Value

Once you have the current price and the number of shares you own, you can easily calculate the total value of your holdings and your gain or loss. This will help you easily see your financial status and portfolio.

Total Value

In the Total Value column, enter the following formula:

=C2*H2

Replace C2 with the cell containing the current price and H2 with the cell containing the number of shares you own. This formula multiplies the current price by the number of shares to give you the total market value of your shares.

Gain/Loss

In the Gain/Loss column, enter the following formula:

=J2-I2

Replace J2 with the cell containing the total value and I2 with the cell containing your cost basis. This formula subtracts your cost basis from the total value to give you your gain or loss on the investment.

Tracking PSE Stocks Specifically

Now, let's talk about tracking stocks listed on the Philippine Stock Exchange (PSE). While Yahoo Finance and Google Finance generally have good coverage, sometimes the stock symbols or data might be slightly different. Usually, though, this is not a problem, and you don't have to worry about it. But remember to check and double-check your data.

Verifying Stock Symbols

Make sure you're using the correct stock symbols for PSE-listed companies. You can find a list of stock symbols on the PSE website or from your broker. Do not assume the ticker is the same as other exchanges. It's best to use an online stock broker that can provide stock data. Some local brokers will even provide an API for your use.

Dealing with Data Discrepancies

Sometimes, you might notice discrepancies between the data in Google Finance and the actual prices on the PSE. This can happen due to delays in data updates or differences in data sources. If you notice significant discrepancies, it's always a good idea to double-check with your broker or a reliable financial news source. You can even cross-reference with other financial websites or apps to ensure you're getting accurate information. Google and other tools can be wrong, so always do your own research.

Automating Your Stock Tracking

To keep your spreadsheet up-to-date, you'll want to automate the data refresh process. Here's how you can do that.

Setting Up Automatic Refresh

Google Sheets has a built-in feature that allows you to automatically refresh your spreadsheet at regular intervals. To set this up:

1. Go to File > Settings.
2. Click on the Calculation tab.
3. In the Recalculation section, choose how often you want the spreadsheet to be recalculated. You can choose from On change, On change and every minute, or On change and every hour.

Important Note: Be careful with the "On change and every minute" option, as it can consume a lot of resources and slow down your spreadsheet if you have a lot of formulas.

Using Scripting for More Advanced Automation

If you want more control over the data refresh process, you can use Google Apps Script. This allows you to write custom scripts to fetch data from other sources or perform more complex calculations. For example, you could write a script to fetch data from a specific financial API and update your spreadsheet automatically.

Tips for Effective Stock Tracking

Here are some additional tips to help you track your stocks effectively:

• Be Consistent: Update your spreadsheet regularly to stay on top of your investments. Make it a habit to check your portfolio at least once a day or once a week.
• Track Your Transactions: Keep a record of all your stock transactions, including the date, price, and number of shares bought or sold. This will help you accurately track your cost basis and calculate your gains or losses.
• Use Charts and Graphs: Visualize your stock data with charts and graphs to identify trends and patterns. Google Sheets has a variety of charting tools that you can use to create informative visualizations.
• Set Alerts: Set up alerts to notify you when certain stocks reach specific price levels. This can help you make timely investment decisions.
• Diversify Your Portfolio: Don't put all your eggs in one basket. Diversify your portfolio by investing in a variety of stocks across different sectors.
• Review and Adjust: Regularly review your portfolio and make adjustments as needed. Your investment goals and risk tolerance may change over time, so it's important to adapt your strategy accordingly.

In Conclusion

So there you have it! Tracking stocks with PSE, Yahoo Finance, and Google Sheets is a powerful way to stay informed about your investments and make smart financial decisions. By following these steps and tips, you can create a customized stock tracking system that meets your specific needs. Remember to always double-check your data, be consistent with your updates, and stay informed about the market. Happy investing!