I wanted to include the value of my home in my assets and could not find an automated way to do it until recently. The way that I implemented this solution expands upon this thread: Home Value for Net Worth - #13 by jpm.moore
Also, this solution impacts the following Tiller sheets:
- Balance History - This sheet might be hidden. I will add a link to unhiding this sheet if it is not available in your Tiller instance.
- Accounts
- Balances
- Net Worth
Here are the steps to implement it:
Pulling the value of your house from Redfin
To successfully test that your pulling the Redfin data correctly, it is recommended that you create a new sheet for testing purposes. You can delete the sheet later.
- Obtain the āRedfin_URLā value by searching for your address at the following URL:
How Much Is My House Worth? | Home Value Estimator | Redfin - Click the home image directly above the value in the middle of the page. See the following example:
- Copy the URL for the home image page into a text file so that you can reference it in the next step.
- Replace āRefin_URLā in the following call with the URL from the previous subset:
=IMPORTXML("Redfin_URL","//*[@class='statsValue']/span")
For example, if my address was 100 Main Street Austin, TX 73301 and Redfin had assigned 12345678 as the reference number for the address, the call would likely be:
=IMPORTXML(āhttps://www.redfin.com/TX/Austin/100-Main-St-73301/home/12345678","//*[@class=āstatsValueā]/spanā)
Note: That is a fictious address.
If you see your home value, proceed to the next section. Otherwise, refer to the thread at the top of the post for pulling home values from Redfin.
Configuring an App Script to automatically retrieve the value
Now that you know your home value according to Redfin, we are going to automate the retrieval of that information on a periodic basis. This automation will enable Tiller to automatically include your home value in the list of assets on the Balances Sheet.
- Click Extensions > Apps Script from the Google Sheets menu.
- Click + in the Files row at the top of the Apps Script page and select Script.
- Enter a title for the script. I chose āHomeValueā.
- Enter the following code for the function in the center section:
function HomeValue() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('2:2').activate();
spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
spreadsheet.getRange('B2').activate();
spreadsheet.getCurrentCell().setFormula('=TODAY()');
spreadsheet.getRange('C2').activate();
spreadsheet.getCurrentCell().setFormula('=TEXT(NOW(),"h:mm AM/PM" )');
spreadsheet.getRange('D2').activate();
spreadsheet.getCurrentCell().setValue('<Address>');
spreadsheet.getRange('F2').activate();
spreadsheet.getRange('F35').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('G2').activate();
spreadsheet.getRange('F35').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('H2').activate();
spreadsheet.getCurrentCell().setValue('<Mortgage_Holder>');
spreadsheet.getRange('I2').activate();
spreadsheet.getCurrentCell().setFormula('<Redfin_Home_Call>');
spreadsheet.getRange('L2').activate();
spreadsheet.getCurrentCell().setValue('INVESTMENT');
spreadsheet.getRange('M2').activate();
spreadsheet.getCurrentCell().setValue('Asset');
spreadsheet.getRange('N2').activate();
spreadsheet.getCurrentCell().setValue('ACTIVE');
};
- In line 11, replace with your street address. For example, 100 Main Street.
- In line 17, replace <Mortgage_Holder> with the name of the company that manages or services your home loan. If you are lucky enough to not have a home loan, enter any value, such as āSelfā. This value will be used as the Institution value on Tillerās Balance History sheet.
- In line 19, replace <Redfin_Home_Call> with the value from the results of the āPulling the value of your house from Redfinā section.
- Click the Save icon at the top of the page. The icon is to the left of the Run icon.
- Click Run. If the script is successful, the execution log at the bottom of the page should show that it started and completed. For example:
- Return to Tiller and open the Balance History sheet. At the top of the sheet, you should now see a value for your home in Row 2. For example:
Configuring an Apps Script trigger
With the expectation that the value is present in the Balance History sheet, letās set up automation to pull the home value on a scheduled basis.
- Return to Apps Script. If the window has closed, click Extensions > Apps Script from the Google Sheets menu.
- Click the Triggers icon on the left side of the Apps Script page. The icon is 4th icon down on the left and resembles an old alarm clock. See:
- Click Add Trigger in the bottom left corner of the page. See:
- Select the name of the script to run from the Choose which function to run menu. For example, āHomeValue,ā which is the name of the script from the āConfiguring an App Script to automatically retrieve the valueā section.
- Select Time-driven from the Select event source menu.
- Select the frequency to run the script from the Select type of time based trigger menu. Because home values do not fluctuate very frequently, I would recommend that you do not run the script more than weekly. Depending on the frequency that you select, additional time-based menus display for further clarification. I also indicated that it should run during the middle of the night (between 1 AM and 2 AM) when there is likely less traffic pulling data from the Redfin site.
- Click Save.
Adding your home to your list of accounts
At this point, if you are leveraging the Accounts sheet, you will find your home in the list of accounts. Complete these steps for the Accounts sheet:
- Select your home from the list of accounts.
- Assign a Call Override value, such as Asset.
- Assign a Group value, such as Real Estate. If you add it to your Accounts sheet with those values, you see your home value listed in the Assets column on the Balances sheet under the REAL ESTATE heading. For example:
It will also impact the information on the Net Worth sheet.
If you have completed all of the steps. you should see the home value be updated in the various Tilller sheets based on the frequency that you indicated for the App Script.
Enjoy!
