Skip to main content


Showing posts with the label Conditional Formatting

Stock Correlation Analysis With Google Sheets

Correlation is a statistical relationship that measures how related the movement of one variable is compared to another variable. For example, stock prices fluctuate over time and are correlated accordingly or inversely to one another. Understanding stock correlation and being able to perform analysis are very helpful in managing a stock portfolio investment. In this post, we will look at how to perform stock correlation analysis with Google Sheets. Understanding correlation and its applications in stock investing Stock correlation analysis with Google Sheets Getting started User guide Conclusion Disclaimer Understanding correlation and its applications in stock investing The most familiar correlation measure is the Pearson product-moment correlation coefficient . The strength of the relationship between two variables is expressed numerically between -1 and 1. For example: Two stocks are positively correlated when their prices always go up or go down together. Their co

Monitor stock portfolio with Google Sheets (Pivot table and GOOGLEFINANCE function)

As an investor, it is important to know the latest state of the stock portfolio. We need to know what stocks currently owned in the portfolio, how many shares for each one, how much dividend or gain contributed so far by each stock, etc. As we have registered stock transactions in a spreadsheet with Google Sheets, we can easily have the latest update from the stock portfolio by using pivot tables and GOOGLEFINANCE function. Use a pivot table to group transactions by symbols Configure the pivot table Positions Use GOOGLEFINANCE function to get real-time information for stocks Conditional formatting columns Demo Note References Disclaimer Use a pivot table to group transactions by symbols The pivot table helps to see relationships between data points. To see how each stock contributes to the portfolio, we will create a pivot table that originated from the Transactions sheet. Select the Transactions sheet. Select the 5 columns A:E . In the menu at the top, click Dat