Macros to make ARV analysis more efficient!
Hi Everyone -
In an effort to determine ARV on a potential fix and flip in an efficient manner, I've asked my r/e agents to send me data extracts from the MLS on the comps. This allows me to avoid manually typing in the information (sold price, concessions, beds, baths, sq ft etc.) for each property into excel. This has saved me significant amount of time.
What I would like to do now is create some sort of macro that can take such information and automate various formatting and calculation functions (based own my own assumptions) to calculate the ARV estimate (something similar to what an appraisal calculation would look like, where the address is presented horizontally, and the inputs vertically starting with the sale price and making adjustments between the subject property and the comps).
I tried doing the "Record Macro" function in excel but Excel gets confused easily. For example, the macro was built recording an analysis I did with 4 comps. If I try to use the macro for an analysis with 5 comps, it doesn't pick up the extra comp.
Is there any kind of (legitimate) add-in that can do the above? If not, are there any programmers out there that could assist me?
Any assistance would be greatly appreciated.
Thanks!
hi George
It sounds like you need to use a for loop as opposed to a discrete entry to have the program find x number of entries/properties. I'm not sure if excel will allow you to do this. I've only done minor excel programming. I have heard of people hiring a programmer to write SQL code for this sort of thing. Good luck
@Richelle T. , thanks for the response. I appreciate it. What do you mean by "for loop" vs "discrete entry".
@Account Closed
A for loop is a loop that sets a premise that for all cases where x is true, y will be executed. It sounds like you have your macro written to iterate 4 times (discrete) instead of for all cases where a condition is true. This is basic programming that you can google. Again, I have only done a small amount of proper programming, not in excel. So I am not sure if excel is robust enough for what you want. I would look into SQL if I were you.
@Account Closed The problem you are trying to solve was the basis for the brokerage that I built. We use MLS data in an investor friendly way. Excel would not be powerful enough to do it on a macro level. You probably better off finding another source that values properties and use their value and go after the best deals based on that. I love your thinking but its not as easy as an excel macro.
Good luck!
@Justin S. , Thanks for the perspective. Are there any programs I can buy that could do what I want?
By the way, I clicked on your website above "Rezamp.com" and added my email address. Looking forward to getting some information from you soon.
@Account Closed There are no programs that I know, it would need to be a custom solution. There are certainly programmers out there that can do it, but the cost would be quite high (talking from experience!). What area are you looking for?
Excel allows you to do custom programming via vba.
All you need to know is how to loop through your data and what your rules are for picking up one of the sold records and using it as a comp. Not to brag, but I think I can write that program in 2 to 4 hours depending on your rules for what you pull in as comps.
So lets say your data has bedrooms, bathrooms, square footage, garage, sold price, concessions, age of home. Some of those criteria would be used to determine whether you should pull it in as a comp and some would be used for further adjustments.
One question though: Do you need it to check distance from the subject property? If so, then you need a custom program to do radius type stuff.
If not, then I would need to know what your rules are for using it as a comp and what your adjustments are. i.e. Can you use a 4bdrm comp against a 3 bdrm home if the sq footage and age are the same and do a 3k adjustment for the bedroom difference?
Also, are you averaging your comps in order to come up with a value?
Are you weighting any comps based on sold date or anything?
What do you want as input for your subject property and output for your comps?
If you send me your spreadsheet and give me that info, I can get that to work for you.
Call it a gift from your fellow BPer.....
Haven't heard back from George as of yet.....
Hey @Mike H. , sorry for the delayed response and thank you very much for your assistance. I would like to private message you on your questions and maybe we could talk through a few of these. Let me know what you think.
@Tom La Rosa , I will keep you posted.
Here is kind of what I'm thinking.
Start the comp data on say row 20 of the spreadsheet.
At the top of the page, have places to enter values of the subject property:
Values used to pull comps: Age of the home, Square footage,
Also, have values enter for the subject property that would be used for adjustments: Bedroom, Bathroom, Basement (Y or N) , Garage (1 or 2 car).
Then have some optional parameters so the user can play with the numbers to retrieve more/less comps. i.e. Square footage range within: - User can enter sq foot range to include. So 100 would bring in comps that are within 100 sq footage of the subject property. Or they can change this value to say 200 to bring in comps within 200 sq ft either.
Do that for square footage and age of home.
Then have user run the macro.
It would come up with an estimated comp for the subject property.
Then it would list the comps used to derive that value.
Address, age of home, square footage, bedroom count, bathroom county, basement, garage, and concessions.
Those are my initial thoughts anyway.
Just sent George the excel file.
Here is a quick definition of what the spreadsheet does. Its a quick and dirty first pass that could easily be modified based on someone's specific needs.
As it is, it allows users to play with variables to decide which comps to pull: i.e. +/- square footage, +/- bedroom count, +/- age of home.
It also allows the user to change the adjustment prices for things like a bedroom, bathroom, garage, basement, etc.
Here are the specs if you will of what you need to enter and what you can play with. And, of course, what the app will return:
Subject Property Fields that must be entered
Year built
Square footage
"Bedroom Count"
"Bathroom Count"
"Garage (0, 1, or 2)"
Basement (Y, N, F)
Variables that can be set to pull comps against
+/- years built
+/- square footage
+/ bedroom count
Dollar adjustments that can be set for comp vs subject
per sq ft difference
1 bedroom difference
Per bath difference. Accounts for 1/2 bath differences as well.
Per 1 car garage difference. Accounts for no garage as well. i.e. 2 car versus 0 is twice the adjustment amount.
Basement Yes versus No. Basement adjustments are cumulative. Y or N is one adjustment. F or N is a second adjustment
"Additional: basement Finished vs Not Finished"
Days on Market over > 60. These DOM adjustments are separate.If over 60 and less than 101, then do the 1st. If over 100, apply the second.
Days on Market over >100
Comp Data
Address:
Year Built
Square footage
"Bedroom Count"
"Bathroom Count"
"Garage (0, 1, or 2)"
Basement (Y, N, F)
Sold Date
Concessions
Actual SOLD PRICE
DOM
"Calculated amount used for estimate (includes the adjustments)"
Application Returns the following to the USER
1. Estimated appraisal
2. Number of comps used
3. Avg square footage of comps used
4. And it highlights each row/comp that was selected
Hi
@Mike H.
Very good. I would love to see it.
Being a 14 year construction guy, I have never used excel. In my REI journey, Ive got a couple of excel files and opened them up, but havent really used them.
TL
Sure. I send you a connection request with my email.
Once I get your email, I'll send you a copy. If anybody else is interested in it, let me know, I'll definitely send it out to you as well. Its not like its proprietary or anything. :-)
@Mike H. @Account Closed I'm interested in what became of the Excel data dump and analysis macro that was discussed in this thread. I'm just starting to get into this REI thing and am looking for data. I'm visiting my RE Agent tomorrow morning and am going to request a similar data dump to automate comps. I have a LOT of VBA programming experience and would like to see what you have done already and see if I can contribute to it and share with the group.
Hi @Jim Snyder , Mike developed a macro enabled spreadsheet. I'm happy to send it to you. What is your email address?
@Mike H. and @Account Closed would either of your mind sending that sheet this way? I just sent you both connection requests.
Thanks for putting that together!
@Account Closed can you email me the sheet. Much appreciated.
@Account Closed I would like a copy of the spreadsheet as well!
If anybody wants a copy, shoot me an email at [email protected].
Also, if anybody that gets a copy wants to do any enhancements, let me know.
I'd like to keep it going. I use to be a programmer but no longer so I like
to dabble in it when there's a real purpose for it.
And you can't beat free so ask away. :-)
@Anna Koenig Just shoot me your email. You may have to colleague request me to send me your email. I tried including mine on here but it wouldn't let me.