Google Apps Script (GAS) code to calculate optimal purchases to re-balance a portfolio.

Click here to create a copy of the demo Google Sheet (the required GAS code will also be copied).

Uses LinearOptimizationService for the calculation:

Constraints:

• 0 <= remaining cash <= 10
• Optimal purchase - maximum deviation* <= Delta <= optimal purchase + maximum deviation*

Where maximum deviation is between 0.01% and 0.1% from the target allocation.

Objective:

• Minimize remaining cash

The resulting calculation will provide a suggested purchase that closely matches the target allocation, without resulting in negative cash.

Suppose you currently own:

And your target composition is the following:

Manually enter this data into the yellow highlighted fields as follows:

Or, use the "💲 Rebalance > 📁 Import data from Schwab" custom menu button to import this data from a Schwab CSV export:

If you can buy fractional shares, populate the "Fractional?" column to indicate that.

The rest of the cells that are not highlighted in yellow are automatically populated from price data retrieved from Google Finance using the =GOOGLEFINANCE formula.

The optimal buy and sell orders to meet your desired composition are automatically calculated:

For shares that must be bought or sold in whole numbers, you could simply round the optimal order. But sometimes that would result in negative remaining cash. So, you need to calculate new optimal orders. Click "💲 Rebalance > 🧮 Calculate buy" to calculate these new optimal orders:

The GAS code will use the constraints (i.e., remaining cash must be greater than 0 and the sum of deviations from target must be minimized) to set the optimal orders.

If your Google account is configured to block "less secured apps" (Less secure app access) then you may see a warning like this when running the GAS code:

To dismiss the warning, open the Sheet and go to Extensions > Apps Script > Project Settings, then click the Change project button under the Google Cloud Platform (GCP) Project section. Then:

1. Create a new GCP project or select an existing GCP project.
2. Configure the OAuth consent screen.
3. Add yourself as a test user to the project.
4. Set the GCP project number for this GAS app.

After following these steps, you will be able to use the app.

This is a legacy Excel VBA script I wrote in 2016, to be used for reference only. This code uses the Excel Solver add-in to calculate the optimal purchase, which is slightly better than Google Sheets (Solver uses a non-linear algorithm which allows multiple objectives).

Note the VBA will not work without modifications, the functions defined on it are tied to Excel form controls.