I have a list of addresses in excel and want to automate looking them up on the county assessors website and copying the assessed value back to excel

[jflagle]

Ok. Here is the skinny.

I am using excel 2007. i am a mechanical engineer. I have dabble in macros along time ago. fairly sophisticated with arrays and using visual basic. but, that was a long time ago.... i seem to have forgotten most of the details and kind of starting from scratch. i dont know teh vba language at all as i have been reading up on it. i was hoping that someone has done something similar and would be able to share their code and give me some pointers on things to change to get it working for my situation.

I have about 500 to 1000 addresses listed in column A in excel. For each address (e.g. 12312 WINDING), I copy the address from excel and paste it into the website Property Tax Search

the assessed value then pops up on the following screen of indygov.org. i copy the assessed value from this page. then switch back to the excel sheet and paste it into the right spot in column B.

i then copy the next address from column A in excel, go back to the original search web page by hitting back button or the return to search link. and redo the steps above. i did these manually the first time and it took 5 or 6 hours to get through the entire list. i do these on a regular basis.

one thing to note.... sometimes the address does not produce an assesed value. you can see this if you were to enter some random address. it will not return a result. also, in some other cases, the property may have two assessed values. if this is the case it will not return a result either. it displays this on a totally different formatted page that reuqires you to select the property of interest. so, if either of these cases occurs - then this property should just be skipped.


Excel Sheet example below (it does not have all 500 to 1000 addresses). Note - when copying the address over, i manually delete one space between teh address number and street. there are 2 spaces and should only be 1. 2 spaces will yield a bad search on the website. in addition, i truncate the address. I do include the N or S or E or W prefex. i only copy the first part if there are multiple names in teh street. so for example 6465 Potomac would be what i would search for the first one below. notice, i deleted one space and didnt use the square suffix.

any help would be greatly appreciated. i am at a loss on this one and it looks like it would take me hours upon hours if i had to figure this out from step 1.

Address (column A)	Assessed Value (column B)
6465 POTOMAC SQUARE
1521 N LASALLE ST
350 N 13TH AVE
530 N HAMILTON AVE
1226 CENTRAL AVE
926 GREER ST
769 N WALLACE AVE
3611 N KENWOOD AVE
3850 E MICHIGAN ST
2121 S PENNSYLVANIA ST
4209 E PLEASANT RUN PKWY S. DR
1566 CARROLLTON
332 W 31ST ST
1764 MORGAN ST
1117 N EWING ST
1400-1406 E WASHINGTON ST
666 E WOODRUFF PLACE DR
418 N 18TH AVE
4110 E NAOMI ST
3108 MANOR CT
110 E WASHINGTON ST
2863 N TALBOTT ST

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[bertie]

Hi, and welcome to the forum.

This is to get you started as I don't understand how you handle multiple returns or truncate values.

Start a new Excel workbook and copy your sample data into Sheet1, ColumnA
Press Alt+F11, click Insert=>Module, copy the code below.
The code will take some time to run. I have assumed the Status Bar is visible to display progress updates.

After you test the code re-run.
This time step through the code, F8, to get a better understanding of what it does.
Use Alt+Tab to flick between screens.
uncomment the line .Visible=True

Sub GetAssessmnet()
   Dim ie As Object
   Dim rng As Range              'range to loop through
   Dim strAssessment As String
   Dim url As String             'webpage
   Dim recNumber As Long         'number of records for status bar updates
   Dim recCount As Long          'record being processed
   
   'set up internet explorer
   url = "http://cms.indygov.org/MyAssessedValue/Search.aspx"
   Set ie = CreateObject("InternetExplorer.Application")
   
   'get the number of records to update status bar
   recNumber = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row - 1 'header row
   
   'loop through the range
   Set rng = Sheets("Sheet1").Range("A2")
   Do Until rng = ""
      recCount = recCount + 1
      Application.StatusBar = "Processing: " & recCount & "/" & recNumber & "...please be patient."
      
      With ie
          '.viisible=true
         .navigate url
         'wait for the page to load
         Do Until .ReadyState = 4
             DoEvents
         Loop
      
         'enter the address and click the button
         .document.all("ctl00_header_txtPropertyLocation").Value = rng.Value
         .document.all("ctl00_header_btnView").Click
         
         'wait for the INTERACTIVE page to load
         Do Until .ReadyState = 3  'NB NOTE CHANGE iN READY STATE NUMBER
             DoEvents
         Loop
         
         'output
         On Error Resume Next
            strAssessment = .document.all("ctl00_header_txtTotalAssessed2013").Value
         On Error GoTo 0
         If strAssessment = "" Then
            rng.Offset(, 1).Value = "<not found="">"
         Else
            rng.Offset(, 1).Value = strAssessment
         End If
      End With
      
      'get the next value
      Set rng = rng.Offset(1, 0)
   Loop
   
   'tidy up
   Set rng = Nothing
   Set ie = Nothing
End Sub

Hope this helps get you started,
Bertie</not>

 

[jflagle]

Bertie,

Thanks a ton for your help. I used the code. it works great. It has one minor bug... on about 20% of the addresses it just repeats the assessed value for the previous address. sometimes it may do this 4 or 5 times in a row. it is somewhat random.

For exmaple, the first time i run it - it may give me duplicate assessed values in column B for addressess 4,5,6 and then 13,14, and then 25,26,27 and so on.

i then clear values and run it agian. this time i get dulicate assessed values in column B for 8,9 and 30,31,32 and so on.

Make sense?

Thanks again for your help.
Jake

 

[bertie]

My fault, I forgot to reset the variable after each loop. See highlighted below:

Sub GetAssessmnet()
   Dim ie As Object
   Dim rng As Range              'range to loop through
   Dim strAssessment As String
   Dim url As String             'webpage
   Dim recNumber As Long         'number of records for status bar updates
   Dim recCount As Long          'record being processed
   
   'set up internet explorer
   url = "http://cms.indygov.org/MyAssessedValue/Search.aspx"
   Set ie = CreateObject("InternetExplorer.Application")
   
   'get the number of records to update status bar
   recNumber = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row - 1 'header row
   
   'loop through the range
   Set rng = Sheets("Sheet1").Range("A2")
   Do Until rng = ""
      recCount = recCount + 1
      Application.StatusBar = "Processing: " & recCount & "/" & recNumber & "...please be patient."
      
      With ie
         .navigate url
         'wait for the page to load
         Do Until .ReadyState = 4
             DoEvents
         Loop
      
         'enter the address and click the button
         .document.all("ctl00_header_txtPropertyLocation").Value = rng.Value
         .document.all("ctl00_header_btnView").Click
         
         'wait for the INTERACTIVE page to load
         Do Until .ReadyState = 3  'NB NOTE CHANGE iN READY STATE NUMBER
             DoEvents
         Loop
         
         'output
         On Error Resume Next
            strAssessment = .document.all("ctl00_header_txtTotalAssessed2013").Value
         On Error GoTo 0
         If strAssessment = "" Then
            rng.Offset(, 1).Value = "<not found="">"
         Else
            rng.Offset(, 1).Value = strAssessment
         End If
      End With
      
      'get the next value
      Set rng = rng.Offset(1, 0)
      strAssessment = ""
   Loop
   
   'tidy up
   Set rng = Nothing
   Set ie = Nothing
   Application.StatusBar = "Done"
End Sub

</not>

 

[jflagle]

Bertie,

Thank you. That did get rid of the redundant entries.... I still have one issue. the program is not picking up assessed values for alot of the addresses. I can go in an manually enter these addresses and it works fine on most of them. there are a few that are truly not in there. but the program misses a bunch more. see below for an example of the asseseed values that it did not pick up.

i think it is only waiting so long. if it doenst get the assesed value it goes to the next one? i think it gets worse when i run another computer on the wireless.

if i run the program again, i may not get values for 4716 longwor and 10204 e dedham but then get values for the next two. its kind of random in that sense? not sure what is going on here? can we have the program wait longer for a response before moving to the next - or do you think it is another issue. here is teh website that gives the assesed values if you want to give it a shot... Property Tax Search

Address
4716 N LONGWOR	$53,100.00
10205 E DEDHAM	$80,200.00
8718 CATALINA
4316 VILLAGE
5349 WOOD	$114,700.00
5721 HACIENDA
1521 N LASALLE	$64,300.00
334 ROBTON	$56,900.00
3125 KERCHEVAL
7221 E 35TH ST
7715 HOMESTEAD	$91,300.00
1016 WESTBROOK	$89,000.00
817 TIMBER
350 N 13TH	$99,800.00
5026 W REGENT
8321 KOUSA DR
10013 SANDOVER
530 N HAMILTON
1214 DALE HOLL
8629 BLOOMING
6609 BLACK ANT
8431 TILLY MIL	$195,100.00
6255 WHITE RAB

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

 

[shyy]

Bertie,

Curious as to how you found this information?


.document.all("ctl00_header_txtTotalAssessed2013")

.document.all("ctl00_header_btnView").Click

I also tried running the macro and it doesn't work however if I do a Step through it does work. When I remove the comment from .Visable = True I get an error. Using Excel 2007

Amazing Macro!

 

[bertie]

I have run the code at my end a couple of times and I can't replicate the problem you describe. Here are my results:

Excel 2007
	A	B
1	Address	Assessed Value
2	4716 N LONGWOR	£53,100.00
3	10205 E DEDHAM	£80,200.00
4	8718 CATALINA	£55,600.00
5	4316 VILLAGE
6	5349 WOOD	£114,700.00
7	5721 HACIENDA	£100,600.00
8	1521 N LASALLE	£64,300.00
9	334 ROBTON	£56,900.00
10	3125 KERCHEVAL
11	7221 E 35TH ST	£66,300.00
12	7715 HOMESTEAD	£91,300.00
13	1016 WESTBROOK	£89,000.00
14	817 TIMBER	£109,500.00
15	350 N 13TH	£99,800.00
16	5026 W REGENT	£87,500.00
17	8321 KOUSA DR	£87,200.00
18	10013 SANDOVER	£187,400.00
19	530 N HAMILTON	£41,300.00
20	1214 DALE HOLL	£85,800.00
21	8629 BLOOMING	£153,200.00
22	6609 BLACK ANT	£99,300.00
23	8431 TILLY MIL	£195,100.00
24	6255 WHITE RAB	£102,700.00
Sheet1

We already pause the code until each webpage loads by testing the ReadyState.

The only thing I can think of is the raw data itself. We could trim off leading and trailing spaces in this line:

         'enter the address and click the button
         .document.all("ctl00_header_txtPropertyLocation").Value = Trim(rng.Value)

 

[bertie]

I have re-read you original post and I note that you remove double spaces is some data. I have added a variable to "fix" the data. See highlighted below:

Code any other "fixes" before sending to the webite

Sub GetAssessmnet()
   Dim ie As Object
   Dim rng As Range              'range to loop through
   Dim strAssessment As String
   Dim url As String             'webpage
   Dim recNumber As Long         'number of records for status bar updates
   Dim recCount As Long          'record being processed
   Dim tmp As String
   
   'set up internet explorer
   url = "http://cms.indygov.org/MyAssessedValue/Search.aspx"
   Set ie = CreateObject("InternetExplorer.Application")
   
   'get the number of records to update status bar
   recNumber = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row - 1 'header row
   
   'loop through the range
   Set rng = Sheets("Sheet1").Range("A2")
   Do Until rng = ""
      recCount = recCount + 1
      Application.StatusBar = "Processing: " & recCount & "/" & recNumber & "...please be patient."
      
      With ie
         .navigate url
         'wait for the page to load
         Do Until .ReadyState = 4
             DoEvents
         Loop
         
         'fix the data
         tmp = Trim(rng.Value)
         tmp = Replace(tmp, "  ", " ")
         
         'enter the address and click the button
         .document.all("ctl00_header_txtPropertyLocation").Value = tmp
         .document.all("ctl00_header_btnView").Click
         
         'wait for the INTERACTIVE page to load
         Do Until .ReadyState = 3  'NB NOTE CHANGE iN READY STATE NUMBER
             DoEvents
         Loop
         
         'output
         On Error Resume Next
            strAssessment = .document.all("ctl00_header_txtTotalAssessed2013").Value
         On Error GoTo 0
         If strAssessment = "" Then
            rng.Offset(, 1).Value = "<not found="">"
         Else
            rng.Offset(, 1).Value = strAssessment
         End If
      End With
      
      'get the next value
      Set rng = rng.Offset(1, 0)
      strAssessment = ""
   Loop
   
   'tidy up
   Set rng = Nothing
   Set ie = Nothing
   Application.StatusBar = "Done"
End Sub

</not>

 

[bertie]

Bertie,

Curious as to how you found this information?


.document.all("ctl00_header_txtTotalAssessed2013")

.document.all("ctl00_header_btnView").Click

I also tried running the macro and it doesn't work however if I do a Step through it does work. When I remove the comment from .Visable = True I get an error. Using Excel 2007

Amazing Macro!

The values enclosed in double quotation is the "name" or "id" of the web control.
I use Google Chrome.
Right click the control and select Inspect Element.

I believe Firefox has an add-in which does the same.
Internet Explorer, however, Right click=>View Source and good luck finding it.

 

[jflagle]

Bertie,

thanks for your help. i played around with the website and it does not care if i have trailing spaces etc. either way, i removed them from the data and it still has issues. but i can cut and paste manually and it will find them no problem.

is it possible that my internet cant handle this amount of info all at once. i have a dsl line - wireless and its not terribly fast. 54Mbps is what my computer says right now in the wireless icon in my tray.

it seems that i get alot of returns with no info (blanks) when I am using wireless on another computure eating up some of the bandwidth. is this possible?

on another note, i would like to do a similar project where i go to a standard website and download a picture of the house for each address. any thoughts on what website might have a standard format to snag picture of teh house (maybe google maps, or some realtor site??). thought you might have some ideas.

last thing... i dont do projects like this very often (computer programming). what is the easiest way to learn excel vba. tutorial, book, excel help, etc. i understan the logic to programming, i just dont know where to find all teh proper syntax and commands for excel vba. thought you might know of the best place to start.

thanks for all of your help. i appreciate you taking the time.

Jake