How To Parse an Address in Excel by Macro/VBA

If you have a full address and would like to extract address line, city, state (province), country and zip code (postal code), then you came to the right place. In this blog, we explain how to make an excel file to parse address. If you’re interested in a custom solution for your Excel file, please contact our Excel experts for a free quote.

We used Google Maps API to extract the information from full address. Google has a special address structure. When you run the code, both Google’s address and regular address is displayed.

Demo

Feel free to download a demo of the Excel XLSM file.

How the Excel file works?

  1. In range C1, type the address that you want to parse, for example “1500 Marine Drive, North Vancouver, BC”.
  2. Click “Parse Address” button. In the yellow area marked by “Google Map Address Structure”, the processed address by Google will be displayed.
  3. The green area marked by “Regular Address” shows the actual address divided into
    • Address
    • City
    • State/Provide
    • Country
    • Postal/Zip Code

Development

If you are familiar with VBA Editor and creating buttons, you can copy/paste the following VBA code into your VBA editor. And create a button to execute this function.

Then, install Json Converter developed by https://github.com/VBA-tools/VBA-JSON. i.e. create a module called “JsonConverter” and copy/paste the code written in the Github link in the module.

Once you copy/paste the code and created a button, type an address in range C1 and click the button. The address is parsed!

VBA Code

' Written by bsup.ca

' Extract Full Address to
' Street Number, Route, Neighborhood, Locality, Administrative Area, Country and Postal Code
Sub ExtractAddress()
    Dim sURL As String, sResult As String
    Dim oResult As Variant, oData As Variant, R As Long, C As Long

    ' Clear the sheet
    ActiveSheet.Range("B2:C9").ClearContents
    
    sURL = "http://maps.googleapis.com/maps/api/geocode/json?address=" + _ 
        WorksheetFunction.EncodeURL(Range("C1").Value) + "&sensor=false"
    
    Debug.Print "URL: " & sURL
    sResult = GetHTTPResult(sURL)
      
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(sResult)
     
    Dim element As Variant
    
    R = 1
    
    For Each element In Json("results")(1)("address_components")
        ActiveSheet.Cells(R + 1, 2) = element("types")(1)
        ActiveSheet.Cells(R + 1, 3) = element("long_name")
        R = R + 1
    
    Next element
    
    Set oResult = Nothing
End Sub

' Get the json result
Function GetHTTPResult(sURL As String) As String
    Dim XMLHTTP As Variant, sResult As String

    Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    XMLHTTP.Open "GET", sURL, False
    XMLHTTP.Send
    Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
    sResult = XMLHTTP.ResponseText
    Debug.Print "Length of response: " & Len(sResult)
    Set XMLHTTP = Nothing
    GetHTTPResult = sResult
End Function

Example of Looping through Worksheets, Deleting Rows & Converting to CSV

This VBA is an example of that loops through selected worksheets in a workbook, deletes the top ten rows and exports it as a CSV.

Sub Test_loop()

ActiveWorkbook.Save

Dim ws As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

' Loop through all selected sheets.
For Each ws In ActiveWindow.SelectedSheets

IF ws.name <> "Monday" then
ws.Rows("1:10").Delete ' Delete 10 rows at top of each sheet.
ws.Copy

Range("A1").Interior.Color = 1 ' Format A1 so the top rows are included in the used range and saved
ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges = False

End IF
Next ws

End Sub

How to call the free Yahoo Finance API to get Stock Data (Excel & VBA)

Yahoo Finance API provides a simple way to download stock quotes. The service returns stock data in a CSV. This blog explain how to get stock data from Yahoo Finance using Excel & VBA. If you’re interested in a custom solution for your Excel file, please contact our Excel experts for a free quote.

In this example, we want to obtain the following Stock info of Google, Facebook and Microsoft.

  • Name of company
  • Last trade date
  • Last trade (price only)
  • Dividend Yield
  • P/E Ratio

Pre-req
Make sure Microsoft WinHTTP Services are enable. To do so , open Visual Basic Editor of your xlsm file. Click Tools >> References. And select Microsoft WinHTTP Services.

Demo

You can also download a demo of this Excel file.

Development Process – Step 1

Range A2 to be taken the head cell. Type “GOOG” in range A3, “FB” in A4 and “MSFT” in range A5. Type “n” in range B3, “d1” in range C3, “l1” in D3, “y” in E3 and “r” in F3. A sample shown below.

Excel VBA Yahoo Finance

Step 2

Open Microsoft Visual Basic for Applications on your Excel. Copy the code and paste it on your sheet’s VBA area.

Double click to select all.

Option Explicit

' Example: Requesting Stock info of GOOG, FB and MSFT.
'
' In this example, we want to obtain the following Stock info
'   - Name of company
'   - Last trade date
'   - Last trade (price only)
'   - Dividend Yield
'   - P/E Ratio
'
' Range A2 to be taken the head cell. Type "GOOG" in range A3,
' "FB" in A4 and "MSFT" in range A5. Type "n" in range B3, "d1"
' in range C3, "l1" in D3, "y" in E3 and "r" in F3.
'
' We use the following URL for getting Stock Quote from Yahoo
' URL: http://finance.yahoo.com/d/quotes.csv?s=FB+MSFT&f=snd1l1yr
'
' The CSV file data looks like the following
'
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' "MSFT","Microsoft Corpora","9/4/2013",31.24,2.89,12.36
'
' The columns in CSV file is separated by comma. Split function
' is used to split the data on each line of CSV file at every
' occurrence of comma. However, some commas in the CSV file are
' part of a string, not separating the columns. For example
' consider the following line,
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' The second comma is not a column separator. But it's part
' of the company's name "Facebook, Inc."
' This program will identify which comma is a column separator
' and which is part of a string

' ************
' Written by http://www.msexcelvancouver.com
' Thanks to http://www.youtube.com/watch?v=iSlBE3CWg5Q

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

    Dim head As Range
    Set head = Range("A2")
    
    Dim i As Integer
    Dim Symbols As String: Symbols = ""
    Dim SpecialTags As String: SpecialTags = ""
    Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://download.finance.yahoo.com/d/quotes.csv?s="
    Dim rng As Range
    Dim cell As Range
    
    ' Get the Stock Symbols
    Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
    For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
        Symbols = Symbols & cell.Value & "+"
    Next cell
    Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'
    
    ' Get the Special Tags
    Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
    For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
        SpecialTags = SpecialTags & cell.Value
    Next
    
    ' Put the desciption/name of each tag in the cell above it
    Dim SpecialTagsArr() As String: Dim TagNamesArr() As String
    Call Get_Special_Tags(SpecialTagsArr, TagNamesArr)
    For Each cell In rng
        cell.Offset(-1, 0).Value = FindTagName(cell.Value, SpecialTagsArr, TagNamesArr)
    Next
    
    
    Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags
    
    Call Print_CSV(Yahoo_Finance_URL, head)
    
End Sub

' Download the CSV file and Print it in the cells
Sub Print_CSV(URL As String, head As Range)

    ' Get the CSV file from Yahoo Finance
    Dim Http As New winhttprequest
    Http.Open "GET", URL, False
    Http.send
    
    Dim Resp As String: Resp = Http.responsetext
    Dim Lines As Variant: Lines = Split(Resp, vbLf)
    Dim sLine As Variant
    Dim Values As Variant
    Dim Value As Variant
    
    Dim i As Integer: i = 1
    Dim next_column As Boolean: next_column = True
    Dim col As Integer
    Dim tmp As String
    
    ' Extract CSV file Data to Cells
    For Each sLine In Lines
        Values = Split(sLine, ",")
        col = 1
        tmp = ""
        For Each Value In Values
            ' Read the info for this program which explains the lines below
            tmp = tmp & Value
            If Left(Value, 1) = """" And Right(Value, 1) <> """" Then next_column = False
            If Not next_column And Right(Value, 1) = """" Then next_column = True
            If next_column Then
                head.Offset(i, col).Value = Replace(tmp, Chr(34), "") ' Remove '"'
                tmp = ""
                col = col + 1
            End If
        Next Value
        i = i + 1
    Next sLine
End Sub

' Find the name of the given tag
Function FindTagName(tag As String, SpecialTags() As String, Tag_Name() As String) As String
    Dim pos As Integer
    Dim tagName As String
    
    On Error GoTo CannotFindTagName
        
    pos = Application.Match(tag, SpecialTags, False) - 1
    
    tagName = Tag_Name(pos)
    FindTagName = tagName
    Exit Function
    
CannotFindTagName:
    MsgBox tag & " not found! Program terminates."
    End
End Function

The following function includes two arrays, one containing the special tags and another containing tag names. You could use these arrays as a reference.

Double click to select all.

' Yahoo Finance Special Tags & Names
Sub Get_Special_Tags(ByRef SpecialTags() As String, ByRef Tag_Name() As String)

    ReDim SpecialTags(0 To 84) As String
    ReDim Tag_Name(0 To 84) As String

    SpecialTags(0) = "a": Tag_Name(0) = "Ask"
    SpecialTags(1) = "a2": Tag_Name(1) = "Average Daily Volume"
    SpecialTags(2) = "a5": Tag_Name(2) = "Ask Size"
    SpecialTags(3) = "b": Tag_Name(3) = "Bid"
    SpecialTags(4) = "b2": Tag_Name(4) = "Ask (Real-time)"
    SpecialTags(5) = "b3": Tag_Name(5) = "Bid (Real-time)"
    SpecialTags(6) = "b4": Tag_Name(6) = "Book Value"
    SpecialTags(7) = "b6": Tag_Name(7) = "Bid Size"
    SpecialTags(8) = "c": Tag_Name(8) = "Change & Percent Change"
    SpecialTags(9) = "c1": Tag_Name(9) = "Change"
    SpecialTags(10) = "c3": Tag_Name(10) = "Commission"
    SpecialTags(11) = "c6": Tag_Name(11) = "Change (Real-time)"
    SpecialTags(12) = "c8": Tag_Name(12) = "After Hours Change (Real-time)"
    SpecialTags(13) = "d": Tag_Name(13) = "Dividend/Share"
    SpecialTags(14) = "d1": Tag_Name(14) = "Last Trade Date"
    SpecialTags(15) = "d2": Tag_Name(15) = "Trade Date"
    SpecialTags(16) = "e": Tag_Name(16) = "Earnings/Share"
    SpecialTags(17) = "e1": Tag_Name(17) = "Error Indication (returned for symbol changed / invalid)"
    SpecialTags(18) = "e7": Tag_Name(18) = "EPS Estimate Current Year"
    SpecialTags(19) = "e8": Tag_Name(19) = "EPS Estimate Next Year"
    SpecialTags(20) = "e9": Tag_Name(20) = "EPS Estimate Next Quarter"
    SpecialTags(21) = "f6": Tag_Name(21) = "Float Shares"
    SpecialTags(22) = "g": Tag_Name(22) = "Day's Low"
    SpecialTags(23) = "h": Tag_Name(23) = "Day's High"
    SpecialTags(24) = "j": Tag_Name(24) = "52-week Low"
    SpecialTags(25) = "k": Tag_Name(25) = "52-week High"
    SpecialTags(26) = "g1": Tag_Name(26) = "Holdings Gain Percent"
    SpecialTags(27) = "g3": Tag_Name(27) = "Annualized Gain"
    SpecialTags(28) = "g4": Tag_Name(28) = "Holdings Gain"
    SpecialTags(29) = "g5": Tag_Name(29) = "Holdings Gain Percent (Real-time)"
    SpecialTags(30) = "g6": Tag_Name(30) = "Holdings Gain (Real-time)"
    SpecialTags(31) = "i": Tag_Name(31) = "More Info"
    SpecialTags(32) = "i5": Tag_Name(32) = "Order Book (Real-time)"
    SpecialTags(33) = "j1": Tag_Name(33) = "Market Capitalization"
    SpecialTags(34) = "j3": Tag_Name(34) = "Market Cap (Real-time)"
    SpecialTags(35) = "j4": Tag_Name(35) = "EBITDA"
    SpecialTags(36) = "j5": Tag_Name(36) = "Change From 52-week Low"
    SpecialTags(37) = "j6": Tag_Name(37) = "Percent Change From 52-week Low"
    SpecialTags(38) = "k1": Tag_Name(38) = "Last Trade (Real-time) With Time"
    SpecialTags(39) = "k2": Tag_Name(39) = "Change Percent (Real-time)"
    SpecialTags(40) = "k3": Tag_Name(40) = "Last Trade Size"
    SpecialTags(41) = "k4": Tag_Name(41) = "Change From 52-week High"
    SpecialTags(42) = "k5": Tag_Name(42) = "Percebt Change From 52-week High"
    SpecialTags(43) = "l": Tag_Name(43) = "Last Trade (With Time)"
    SpecialTags(44) = "l1": Tag_Name(44) = "Last Trade (Price Only)"
    SpecialTags(45) = "l2": Tag_Name(45) = "High Limit"
    SpecialTags(46) = "l3": Tag_Name(46) = "Low Limit"
    SpecialTags(47) = "m": Tag_Name(47) = "Day's Range"
    SpecialTags(48) = "m2": Tag_Name(48) = "Day's Range (Real-time)"
    SpecialTags(49) = "m3": Tag_Name(49) = "50-day Moving Average"
    SpecialTags(50) = "m4": Tag_Name(50) = "200-day Moving Average"
    SpecialTags(51) = "m5": Tag_Name(51) = "Change From 200-day Moving Average"
    SpecialTags(52) = "m6": Tag_Name(52) = "Percent Change From 200-day Moving Average"
    SpecialTags(53) = "m7": Tag_Name(53) = "Change From 50-day Moving Average"
    SpecialTags(54) = "m8": Tag_Name(54) = "Percent Change From 50-day Moving Average"
    SpecialTags(55) = "n": Tag_Name(55) = "Name"
    SpecialTags(56) = "n4": Tag_Name(56) = "Notes"
    SpecialTags(57) = "o": Tag_Name(57) = "Open"
    SpecialTags(58) = "p": Tag_Name(58) = "Previous Close"
    SpecialTags(59) = "p1": Tag_Name(59) = "Price Paid"
    SpecialTags(60) = "p2": Tag_Name(60) = "Change in Percent"
    SpecialTags(61) = "p5": Tag_Name(61) = "Price/Sales"
    SpecialTags(62) = "p6": Tag_Name(62) = "Price/Book"
    SpecialTags(63) = "q": Tag_Name(63) = "Ex-Dividend Date"
    SpecialTags(64) = "r": Tag_Name(64) = "P/E Ratio"
    SpecialTags(65) = "r1": Tag_Name(65) = "Dividend Pay Date"
    SpecialTags(66) = "r2": Tag_Name(66) = "P/E Ratio (Real-time)"
    SpecialTags(67) = "r5": Tag_Name(67) = "PEG Ratio"
    SpecialTags(68) = "r6": Tag_Name(68) = "Price/EPS Estimate Current Year"
    SpecialTags(69) = "r7": Tag_Name(69) = "Price/EPS Estimate Next Year"
    SpecialTags(70) = "s": Tag_Name(70) = "Symbol"
    SpecialTags(71) = "s1": Tag_Name(71) = "Shares Owned"
    SpecialTags(72) = "s7": Tag_Name(72) = "Short Ratio"
    SpecialTags(73) = "t1": Tag_Name(73) = "Last Trade Time"
    SpecialTags(74) = "t6": Tag_Name(74) = "Trade Links"
    SpecialTags(75) = "t7": Tag_Name(75) = "Ticker Trend"
    SpecialTags(76) = "t8": Tag_Name(76) = "1 yr Target Price"
    SpecialTags(77) = "v": Tag_Name(77) = "Volume"
    SpecialTags(78) = "v1": Tag_Name(78) = "Holdings Value"
    SpecialTags(79) = "v7": Tag_Name(79) = "Holdings Value (Real-time)"
    SpecialTags(80) = "w": Tag_Name(80) = "52-week Range"
    SpecialTags(81) = "w1": Tag_Name(81) = "Day's Value Change"
    SpecialTags(82) = "w4": Tag_Name(82) = "Day's Value Change (Real-time)"
    SpecialTags(83) = "x": Tag_Name(83) = "Stock Exchange"
    SpecialTags(84) = "y": Tag_Name(84) = "Dividend Yield"

End Sub

YouTube Video

We also explained how to use this Excel file, on a YouTube Video.

Need some help?

Please feel free to contact our Excel experts if you are looking for some Excel help.