Next is attribute which is home to a variety of potential metrics for Google to retrieve. Getting real-time information on stocks in Google Sheets is very simple as we can utilize the powerful "GOOGLEFINANCE" formula which works as follows:
Here we only need to change the criteria to "Dividend" and the summation column on "RawData" to Column F. Similarly, we can retrieve the cumulative amount of dividends collected for a given security (Column K on "Portfolio_Overview"). If these two criteria are met then Column E on "RawData" should be summed up. To query each of this we build an array formula together with "SUMPRODUCT" and two criteria:ġ) On "RawData" the company must be equal to the company selected on "Portfolio_Overview" (=RawData!$C2:$C1000=$C6).Ģ) Then, we only want to select data for "Buy" transactions (=RawData!$B2:$B1000="Buy") at first. Generally, to get that number we need to subtract the number of sold shares from the number of purchased shares. In Column D we want to get the number of shares we hold for a specific ticker defined in Column C. While in Excel we could easily do that via "GetPivotData" formulas, in Google Sheets we can use the powerful method of "ARRAFORMULAS" formulas. The page is designed in a way that users only have to define a ticker and a sector and then all other information is pulled automatically, either from the "RawData" sheet or from online sources.įor a selection of columns we need to calculate information retrieved from the "RawData" sheet. On this page we want to keep track of income and performance with minimum effort. Without further ado here is the link to the most current version of our Google Sheet: click here Make sure to download a local copy to input your own data. As a result, two new sheets "Portfolio_Overview" and "Stock_Analysis" have been integrated into the Google Sheet.Īlso, I will show how one can easily import any tables from various HTML sources using the dividend history of Coca-Cola ( KO) as a prime example. Having laid the basics in part 1 we will now focus on how to meaningfully aggregate our raw data and combine that with real-time stock and dividend information.
0 kommentar(er)
