Skip to main content

Posts

Google Apps Script tips and tricks

When working with Google Apps Script, there are some tasks that I need to perform quite often. For that, I create a list of snippets that I can easily copy whenever needed. In this post, I share my useful snippets and my tips and tricks when working with apps script. If you have any snippet/tip/trick, you can share in the comment section. Effective use of the GOOGLEFINANCE function to get historical prices of stocks in a spreadsheet (Sheets, Apps Script) Clean empty cells for a sheet (Sheets, Apps Script) Get letter for a column index (Sheets, Apps Script) Copy a sheet as HTML table (Sheets, Apps Script) Add a menu on opening a spreadsheet (Sheets, Apps Script) Hide a sheet by name (Sheets, Apps Script) Show a sheet by name (Sheets, Apps Script) Create a new sheet or clear content of an old sheet if exists (Sheets, Apps Script) Effective use of the GOOGLEFINANCE function to get historical prices of stocks in a spreadsheet (Sheets, Apps Script) As I manage my stock port
Monitor stock portfolio with Google Sheets (Pivot table and GOOGLEFINANCE function)

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 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 Data and then