NOgmacks wrote: ↑Thu Jul 20, 2017 2:28 pm
vitaflo wrote:
Scripts in Google Sheets can do a lot (I wrote one to email me when my AA gets out of whack by a certain %, checks every day), but they're not trivial.
This sounds awesome. Didn't know about scripts. Any way you can share this particular script you wrote? Maybe gives me a direction to start with. You will take away the personal part of the logic of course.
I will share a my simple script to send emails, but if you don't know programming, you may run into trouble, just a caveat.
To start you need to go to your spreadsheet and in the menu go to
Tools > Script Editor. This is where you'll be writing code to check your AA and send you an email if it's out of whack. Here's an example:
Code: Select all
function readCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("MySheet"); // sheet name with your AA values in it
var TotValue = sheet.getRange("A1").getValue() * 100; //make value in A1 into percent integer
TotValue = TotValue.toFixed(2); //round to 2 places.
var StockValue = sheet.getRange("B2").getValue() * 100; //make value in B2 into percent integer
StockValue = StockValue.toFixed(2); //round to 2 places.
if (TotValue >= 5.0) { //if off by more than 5%
sendMail(TotValue, "TOTAL"); // send email
}
if (StockValue >= 5.0) { //if off by more than 5%
sendMail(StockValue, "Stock"); // send email
}
};
function sendMail(value, allocation) {
var email = "user@example.com"; //your email
var subject = allocation + " Allocation Alert: " + value + "%";
var body = "Your " + allocation + " asset allocation is off by " + value + "%";
MailApp.sendEmail(email,subject,body);
}
"MySheet" is the name of the sheet you're targeting.
"A1" and
"B2" are the cells where you've calculated how far out of whack your AA is for your total portfolio (stocks to bonds) and stocks (US to Int). The script will check these values and if they are >5% (
5.0 in the "if" statement) it will call the
sendMail() function (to
"user@example.com" in this case). Use your own values in the above, these are just for example.
For this to run you need to set a trigger. Save the script and then in the menu go to
Edit > Current Project's Triggers. You will need to create a new trigger. It should default to run
readCell() which is what you want. For
Events you want it set to
Time Driven > Day Timer > X-X, where
"X-X" is a time you want the trigger to run (mine is set for
9am-10am for example). During that time frame every day the script will run and if your AA is out of whack by 5% or more it will send you an email saying so.
When you save the trigger it will ask you to authorize and view permissions. Start this process and eventually you will get to a screen saying the app isn't authorized. Hit
Advanced here and click on
"Go to Script Name (unsafe)". You will then get a screen asking for you to allow the script to run, do so (don't worry, it's not unsafe, just don't share the sheet with anyone). Your script is now hooked up and running.
You may want to test the script to make sure there are no errors when it runs. To do so, go to
Run > Run Function, and pick either function. Note if you pick
readCell() and your AA isn't off by 5%, you won't get an email. But if there's an obvious error (like you mistyped the sheet name) the script page will bark at you to fix it. You may also want to manually change the values in your sheet to make sure the script runs and sends you the email properly.
Hopefully this helps someone has a little programming experience and wants some automated alerts. It certainly helps me not peek at my portfolio. The portfolio now lets me know when it's time to rebalance, I don't need to check it.