Hey guys! Want to keep a close eye on the Philippine Stock Exchange (PSE) without spending a fortune on fancy software? Well, you're in luck! Google Sheets is a fantastic and free tool that can help you track stock prices, analyze trends, and make informed investment decisions. Let's dive into how you can set up your own PSE stock tracker using Google Sheets.

Setting Up Your Google Sheet for PSE Stock Tracking

First things first, you'll need a Google account (if you don't already have one). Once you're logged in, head over to Google Drive and create a new Google Sheet. Give it a descriptive name, like "PSE Stock Tracker" or something similar. Now, let's get this spreadsheet ready to pull in some real-time stock data.

Getting Stock Data into Google Sheets

Here's where the magic happens. Google Sheets has a built-in function called GOOGLEFINANCE that allows you to retrieve current and historical stock information directly from Google Finance. This function is super powerful and easy to use. Here's the basic syntax:

=GOOGLEFINANCE("TICKER", "ATTRIBUTE", "START_DATE", "END_DATE", "INTERVAL")

Let's break down each part:

• TICKER: This is the stock symbol you want to track. For Philippine stocks, you'll typically use the PSE ticker symbol. For example, BDO Unibank is "BDO".
• ATTRIBUTE: This specifies what kind of data you want to retrieve. Common attributes include "price", "high", "low", "volume", and "marketcap".
• START_DATE: (Optional) If you want historical data, enter the start date in the format "YYYY-MM-DD".
• END_DATE: (Optional) If you want historical data, enter the end date in the format "YYYY-MM-DD". If you omit this, it defaults to today's date.
• INTERVAL: (Optional) Specifies the frequency of historical data (e.g., "DAILY", "WEEKLY").

Example: Getting the Current Price of BDO

To get the current price of BDO, simply enter the following formula into a cell:

=GOOGLEFINANCE("BDO", "price")

Google Sheets will automatically fetch the current price from Google Finance and display it in the cell. Pretty cool, huh? You can repeat this process for other PSE-listed companies, like Ayala Corporation (ALI), SM Investments Corporation (SM), and PLDT (TEL).

Adding More Stock Information

To enhance your stock tracker, you can add more columns for different attributes. For example, you could have columns for the high price, low price, volume, and market capitalization. The formulas would look like this:

• High Price: =GOOGLEFINANCE("BDO", "high")
• Low Price: =GOOGLEFINANCE("BDO", "low")
• Volume: =GOOGLEFINANCE("BDO", "volume")
• Market Capitalization: =GOOGLEFINANCE("BDO", "marketcap")

Just remember to replace "BDO" with the ticker symbol of the stock you want to track. With these key metrics in your Google Sheet, you're well on your way to monitoring your favorite PSE stocks like a pro. It's also important to note that the data provided by GOOGLEFINANCE is delayed by up to 20 minutes, so it's not suitable for high-frequency trading.

Tracking Historical Stock Data

Beyond just current prices, tracking historical data is essential for identifying trends and making informed predictions. Google Sheets makes this straightforward with the GOOGLEFINANCE function.

Retrieving Historical Data

To retrieve historical data, you'll need to specify the START_DATE and END_DATE parameters in the GOOGLEFINANCE function. For example, to get the daily closing prices of BDO from January 1, 2023, to January 31, 2023, you would use the following formula:

=GOOGLEFINANCE("BDO", "price", "2023-01-01", "2023-01-31", "DAILY")

This formula will return a table with the dates and corresponding closing prices for BDO during that period. Google Sheets will automatically create a table with two columns: Date and Price. You can then use this data to create charts and graphs to visualize the stock's performance over time.

Creating Charts and Graphs

Visualizing historical data can help you spot trends and patterns that might not be obvious from looking at raw numbers. Google Sheets has excellent charting capabilities that you can use to create informative visualizations.

1. Select the Data: Select the range of cells containing the historical data you retrieved using GOOGLEFINANCE. This should include both the dates and the stock prices.
2. Insert a Chart: Go to the "Insert" menu and select "Chart". Google Sheets will automatically suggest a chart type based on your data. A line chart is usually the best choice for visualizing stock prices over time.
3. Customize the Chart: Use the Chart Editor to customize the chart to your liking. You can change the chart type, add titles and labels, adjust the axis scales, and more. For instance, add a title such as “BDO Stock Price Trend (Jan 2023)” to clarify what the chart represents.

Analyzing Trends

By visualizing historical data, you can identify trends such as:

• Uptrends: The stock price is generally increasing over time.
• Downtrends: The stock price is generally decreasing over time.
• Sideways Trends: The stock price is fluctuating within a narrow range.

Identifying these trends can help you make informed decisions about when to buy or sell a stock. Remember, past performance is not necessarily indicative of future results, but it can provide valuable insights.

Enhancing Your PSE Stock Tracker

Now that you have a basic PSE stock tracker set up, let's explore some ways to enhance it with additional features and functionalities.

Adding Conditional Formatting

Conditional formatting allows you to automatically highlight cells based on certain criteria. This can be useful for quickly identifying stocks that are performing well or poorly.

For example, you could set up conditional formatting to highlight cells with a price increase in green and cells with a price decrease in red. Here's how:

1. Select the Cells: Select the range of cells containing the stock prices you want to format.
2. Open Conditional Formatting: Go to the "Format" menu and select "Conditional formatting".
3. Set the Rules: In the Conditional format rules pane, you can set the criteria for formatting. For example, you could set a rule to format cells greater than a certain value with a green background and cells less than a certain value with a red background.

Creating a Dashboard

A dashboard is a visual summary of your key stock data. It can include charts, graphs, and key performance indicators (KPIs) that give you an overview of your portfolio's performance.

To create a dashboard, you can use Google Sheets' charting and data visualization tools. For example, you could create a line chart showing the historical performance of your portfolio, a pie chart showing the allocation of your investments, and a table showing key KPIs such as total return and volatility. With this, your PSE stock tracker will be very helpful.

Setting Up Alerts

Unfortunately, Google Sheets doesn't have a built-in feature for setting up alerts when a stock price reaches a certain level. However, you can use third-party add-ons or scripts to achieve this functionality. Search the Google Workspace Marketplace for add-ons that provide stock price alerts.

Using Google Apps Script

For advanced users, Google Apps Script allows you to automate tasks and add custom functionality to your Google Sheet. For example, you could write a script to automatically refresh stock prices at regular intervals, send email notifications when a stock price reaches a certain level, or integrate your stock tracker with other services.

Best Practices for PSE Stock Tracking with Google Sheets

To ensure that your PSE stock tracker is accurate and reliable, follow these best practices:

• Use Reliable Data Sources: While GOOGLEFINANCE is convenient, it's essential to be aware that the data is delayed and may not be entirely accurate. Consider using other data sources, such as Bloomberg or Reuters, for more accurate and up-to-date information. Some of these sources may require a subscription.
• Verify Data Accuracy: Always double-check the data you're getting from GOOGLEFINANCE or other sources to ensure that it's accurate. You can compare the data with other sources or consult with a financial professional.
• Update Your Tracker Regularly: Stock prices can change rapidly, so it's essential to update your tracker regularly to stay informed. You can manually refresh the data or use a script to automate the process.
• Protect Your Data: Your stock tracker may contain sensitive financial information, so it's essential to protect it from unauthorized access. Use a strong password for your Google account and consider enabling two-factor authentication.

Disclaimer

This article is for informational purposes only and does not constitute financial advice. Investing in the stock market involves risk, and you could lose money. Always consult with a financial professional before making any investment decisions. Happy tracking, and may your investments flourish! Remember to do your own research, and never invest more than you can afford to lose. I hope this guide helps you create an effective and informative PSE stock tracker using Google Sheets!