Tiller and Fidelity Cash Management Account

Ask Anything
1

I’m considering moving our checking account from Wells Fargo to a Fidelity Cash Management Account.

If you use Tiller with a Fidelity Cash Management account, does it sync reliably with Tiller?

2

I did the same recently and it’s worked perfectly for me. Fidelity is an open banking connection, so excellent syncing :slight_smile:

Since it’s really a brokerage account (that’s connected to a UMB bank account), it does have extra transactions moving funds into/outof the core fund.

I AutoCat them like this, and delete them:

image
image785×69 8.26 KB

^PURCHASE INTO CORE|^REDEMPTION FROM CORE

1 Like
3

I have a Fidelity Cash Management account that I have had linked to Tiller for almost 5 years and it syncs perfectly. It’s a great account and the debit card/free Atms is a nice part of this. It will not work with Zelle. Works with Venmo and Paypal.

Like @Mark.S shows, you do see some extra transactions in/out of the core fund. This started when Fidelity moved to Open Banking. I just delete those entries when they show up.

1 Like
4

I also mark in autocat and then use a filter in transactions to group, mark and delete. Wondering if there’s an easier way - like: autodelete in autocat, create a auto delete button in transactions? any ideas.

5

I actually use a time driven script to auto delete them after auto fill. I use a similar autocat rule for investment accounts where I really just want the balance data. So, it’s fully automatic for me now.

The script does more than that, though, as it also removes extra text I don’t want on split transaction notes. It could be used to auto-replace Description text, too, with regex, which isn’t supported by autocat - that’s something I’m not using ATM, so I don’t have it fully implemented quite yet. A lot people are intimidated by script, though, so I don’t typically offer that up unless it’s requested.

6

Sounds like what I’m looking for - usually I can work thru code from an example and customize for me. If you would kindly forward. thanks, mark

7

Here you go. Note the three variables at the top can be used for some control. The dontChangeToday variable gives me the option to preview today’s AutoCat result, before changes occur the next day … I’ll enable that, if I’m playing with some new AutoCat rule and want to check it’s working as intended.

function cleanTransactions() {
  var DEBUG = false;           // Set true to disable sheet modifications
  var dontChangeToday = true;  // Set true to only change past data (doesn't change today's data)
  var lastRow = 100;           // Specify the last row to process

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transactions");
  var data = sheet.getDataRange().getValues();
  var header = data[0];
  var totalRowsBefore = data.length;

  // Find the column indexes for "Description", "Note", and "Date Added"
  var descriptionCol = header.indexOf("Description") + 1;
  var noteCol = header.indexOf("Note") + 1;
  var dateAddedCol = header.indexOf("Date Added") + 1;

  var today = new Date();
  today.setHours(0, 0, 0, 0);  // Normalize time for comparison

  var lastRowIndex = Math.min(lastRow - 1, data.length - 1);  // Subtract 1 to match array index

  // Ensure at least one of the columns exists
  if (descriptionCol > 0 || noteCol > 0) {
    var deletedRows = [];
    var noteChangedRows = [];
    var descChangedRows = [];
    var totalDeleted = 0;
    var totalNotesChanged = 0;
    var totalDescChanged = 0;
    var firstRowProcessed = null;
    var lastRowProcessed = null;

    // Phase 1: Process rows for deletions or Note changes
    for (var i = 1; i <= lastRowIndex; i++) {
      if (firstRowProcessed === null) firstRowProcessed = i + 1;  // Set the first row processed
      lastRowProcessed = i + 1;  // Keep updating the last row processed

      var row = data[i];
      var dateAddedValue = dateAddedCol > 0 ? new Date(row[dateAddedCol - 1]) : null;

      // Skip the row if dontChangeToday is enabled and it's today's data
      if (dontChangeToday && !(dateAddedValue < today)) {
        continue;
      }

      // Process Description column if it exists
      if (descriptionCol > 0) {
        var descriptionValue = row[descriptionCol - 1];

        var updatedDescription = descriptionValue.replace(/some pattern to replace/, 'replacement');
        if (descriptionValue !== updatedDescription) {
          if (!DEBUG) {
            sheet.getRange(i + 1, descriptionCol).setValue(updatedDescription);
          }
          descChangedRows.push(i + 1);
          totalDescChanged++;
        }

        // Check if the row should be deleted
        if (descriptionValue === 'DELETEME') {
          deletedRows.push(i + 1);  // Store original row index
        }
      }

      // Process Note column if it exists
      if (noteCol > 0) {
        var noteValue = row[noteCol - 1];
        var updatedNote = noteValue.replace(/split .* transaction.*/, 'split');
        if (noteValue !== updatedNote) {
          if (!DEBUG) {
            sheet.getRange(i + 1, noteCol).setValue(updatedNote);  // Update only the Note column cell
          }
          noteChangedRows.push(i + 1);  // Log original row index where the note was changed
          totalNotesChanged++;
        }
      }
    }

    // Phase 2: Delete rows from bottom to top to avoid index shift
    if (deletedRows.length > 0) {
      deletedRows.sort((a, b) => b - a);  // Sort in descending order to delete from the bottom
      deletedRows.forEach(function(row) {
        if (!DEBUG) {
          sheet.deleteRow(row);
        }
        totalDeleted++;
      });
    }

    // Adjust `noteChangedRows` after deletion
    for (var j = 0; j < deletedRows.length; j++) {
      var deletedRow = deletedRows[j];
      noteChangedRows = noteChangedRows.map(function(row) {
        return row > deletedRow ? row - 1 : row;  // Adjust for each deleted row
      });
    }

    // After all rows are processed and deletions are complete, recheck the sheet for total rows
    var totalRowsAfter = sheet.getLastRow();  // This will get the number of rows in the sheet after deletions

    // Log results
    noteChangedRows.sort((a, b) => a - b);  // Sort in ascending order
    Logger.log('Transactions sheet:');
    Logger.log('Row numbers processed: ' + firstRowProcessed + " to " + lastRowProcessed);
    Logger.log('Row numbers deleted: ' + deletedRows.reverse().join(', '));  // Log deleted rows in ascending order
    Logger.log('Row numbers Note changed (after row deletes): ' + noteChangedRows.join(', '));
    Logger.log('Number of rows before/after: ' + totalRowsBefore + ' -> ' + totalRowsAfter);

    var totalChanges = totalDeleted + totalNotesChanged + totalDescChanged;
    var paddingLength = String(totalChanges).length;
    var logLines = [];
    logLines.push('Change Counts:')
    logLines.push(padLeft(totalDeleted, paddingLength) + " Total number of rows deleted");
    logLines.push(padLeft(totalNotesChanged, paddingLength) + " Total number of rows Note changed");
    logLines.push(padLeft(totalDescChanged, paddingLength) + " Total number of rows Description changed");
    logLines.push("-".repeat(paddingLength + 34));  // Adjust the length of the separator line to match the logs
    logLines.push(padLeft(totalChanges, paddingLength) + " Total Changes");
    Logger.log(logLines.join('\n'));

  } else {
    Logger.log('Error: Neither Description nor Note column found!');
  }
}

// Function to pad numbers for right alignment
function padLeft(number, totalLength) {
  return String(number).padStart(totalLength, ' ');
}
1 Like