How Do I Import Estimated Home Values From Eppraisal.com Into Excel?

[George Keen]

Greetings from Ohio!

 

I'm new to this forum and to Macro Express, so I'm looking for some "beginners" tips here. Hope you all can help me.

 

I'm trying to figure out how to paste a long list of home addresses into Excel, using Macro Express 3.6 They will be in the following format: "xxx Anystreet Rd, City" or "888 Spinning Rd, Dayton". (leaving off the state).

 

I would also like to automatically pull the Estimated Home Values off of Eppraisal.com putting them into Excel to the right side of the address, into the next three columns, using some kind of a Macro.

 

I sure would appreciate any help you might be able to offer. Thanks in advance.

 

George

[Yehnfikm8Gq]

The second task will need some research. For the first, what does the current list look like?

A text file with each address on a new line

A really long sentence with the whole lot separated by spaces, commas etc

Any of the above with the address in quotes

 

If it's a simple text file you can import directly into Excel via File Open and follow the dialogs or Data/Get External Data. Success may depend on any extraneous characters. You can format the spreadsheet after pasting.

 

If there are unwanted spaces etc it will be best to get rid of them before putting in Excel.

[Yehnfikm8Gq]

For Eppraisal.com you need the Address, City and State. I did not try without State but many city names exist in several States. You did not detail how the State information is presented.

 

The level of automation will dictate how complicated it becomes. Below is a rough outline. I can't spend too much time being very specific because this is a considerable task. It's like asking someone to explain relativity in an hour. You'll have to slog through it and ask for help where necessary.

 

Let's say you have Address, City, State in 3 columns

Open "http://www.eppraisal.com" (Text type into browser address bar, enter)

Open the spreadsheet in Excel

T10=A

T12=3

 

Repeat until T1="" (absolutely nothing, blank)

Let's say the first house address is in A3-C3 (see above and bottom for locating first cell)

Copy Address into T1, City into T2, State into T3 (right arrow to move between cells)

Append "," to T2

Append T3 to T2 (eg Orlando,FL)

Activate your browser window

Text Type Tab (select with mouse in dialog, it wil appear <TAB>)

Text Type T1

Wait for Text Playback

Text Type Tab

Text Type T2

Wait for Text Playback

Text Type Enter

Wait for Window Title (partial match on the street address T1, enter as %T1%)

Small time delay (0.5 secs)

Text Type Tab Tab Tab Tab (should highlight Home Values)

Text Type Shift Down

Mouse move (directly below, about 1/4 the way up the box, you may be able to push it to 1/2 way)

Mouse left click (selects about 4 visible lines of text)

Text Type Shift Up

Copy to Clipboard

Set Variable T5 to Clipboard content

Find position of $ in T5, say N5

N5=N5-1 (omit if you don't want the $)

Delete part of T5 from character 1 to N5

Find position of character ASCII value 13 (CR - end of line) in T5, say N5

N5=N5-1

Copy part of T5 into T6 from 1 to N5

You could perform a check at this point to check the value has been copied properly (omitted). During testing, put a Text Box Display here showing T6

Send browser back to Eppraisal home page

Activate Excel

Right Arrow (to D3)

CopyT6 to Clipboard (you may have to convert T6 to integer depending on Excel formatting and whether you include $)

Paste (into D3)

Use arrows (one down, three left) to go to A4 for next address.

 

Alternatively use Text Type Ctrl+G in Excel and enter A4. You can put the starting and repeat cells into a variable

T11=T10 (T10 is always A, set before the start of the Repeat)

(T12 starting value of 3 set before the start of the Repeat)

Append T12 to T11 (A3) - use this to locate cell

Convert T12 to integer N12

N12=N12+1 (now 4)

Convert N12 to text T12

Next time through T11 will come out A4

 

Repeat End

Save the Spreadsheet etc

 

You may have to allow time for the Eppraisal home page to load. Messing with webpages is fraught with problems. You may have to add lots of small time delays for things to occur.

 

If the address is not found that's another load of logic. Try getting to work first with recognized addresses.

[George Keen]

Wow John, thanks for that informed response. It sure isn't easy is it? I'll see what I can do with that, and let you know how it turns out.

 

Thanks again!

[Yehnfikm8Gq]

Good luck! I've forgotten all about that code. It looks far worse on paper. Normally, if you program as you go this would be fairly routine although lengthy to troubleshoot. Based on how many you intend to do, you will have to decide if manual entry is quicker overall.