How To Scrap A Website Keywords? – Keyword Scraping Excel

Web Scraping & Keywords Analysis Tool – SEO

Get the Excel with VBA code for Scraping Keywords from Website to help in your SEO tasks.

Search Engine Optimization (SEO) is the process of making web page content understandable to search robots. Then only, they can realize the purpose of our website and index our pages in a better position. But these days, it has become more of scraping keywords optimization or tweaking to bring websites in the 1st place of SERP (Search Engine Result Page).

Below topics mainly explains basic SEO tips on how to place your keywords in your content that will improve its meaning to Humans & also to Computers.

Keyword Scraping Excel – VBA Web Scraping

In one of my earlier articles, it is explained how to download webpage content with VBA. We are going to re-use that code here in the Keyword scraping Excel App, download content, parse the HTML tags and present the website data analytic.

1. Open a New Excel workbook.
2. Press Alt + F11 to view VB editor, Copy paste below code there.
3. In the first sheet, Cell B1, Type a valid Website URL.
4. Then, execute the below code by pressing F5 in Excel VBE.

Note: Free code is available only for link & scripts extracting. To analyze keywords & for good search engine optimization solutions:

Option Explicit

'This code is from https://officetricks.com
'Contact officetricks123@gmail.com for purchasing extended code for SEO and other purposes
Public Sub Keyword_Scraping_Excel_SEO()
    Dim iRow As Double, iCol As Double, j As Integer
    Dim Web_URL As String, oHTMLFile As Object, hText As HTMLDocument
    Dim wTag As Object, dword As String
 
    'Replace the URL of the webpage that you want to download
    Web_URL = VBA.Trim(ThisWorkbook.Sheets("Input").Cells(1, 2))
 
    'Create HTMLFile Object
    Set oHTMLFile = CreateObject("htmlfile")
 
    'Get the WebPage Content to HTMLFile Object
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", Web_URL, False
        .send
        oHTMLFile.body.Innerhtml = .responseText
    End With
    Set hText = oHTMLFile
    ThisWorkbook.Sheets("Links_Scripts").Activate
    ThisWorkbook.Sheets("Links_Scripts").Cells.ClearContents

    Dim tagIdx As Integer, wTxt As String
    iCol = 0
        
    'Get H1, H2, H3, H4, H5, H6
    For tagIdx = 1 To 6
        iRow = 0
        For Each wTag In hText.getElementsByTagName("H" & tagIdx)
            If iRow = 0 Then
                iCol = iCol + 1
                iRow = iRow + 1
                ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "H" & tagIdx
            End If
            
            wTxt = wTag.outerText
            If VBA.Trim(wTxt) <> "" Then
                iRow = iRow + 1
                ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = wTag.outerText
            End If
        Next
    Next
    
    'Get Bold Contents
    iRow = 0
    For Each wTag In hText.getElementsByTagName("B")
        If iRow = 0 Then
            iRow = iRow + 1
            iCol = iCol + 1
            ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Bold Text"
        End If
        
        wTxt = wTag.outerText
        If VBA.Trim(wTxt) <> "" Then
            iRow = iRow + 1
            ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = wTag.outerText
        End If
    Next
    
    'Get HREF Outlink URLs from Webpage
    iRow = 1
    For tagIdx = 1 To hText.Links.Length
        If iRow = 1 Then
            iCol = iCol + 1
            ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Links"
        End If
        iRow = iRow + 1
        ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = hText.Links.Item(tagIdx - 1).href
    Next
    
    'Get Scripts present in Webpage
    iRow = 1
    For tagIdx = 1 To hText.Scripts.Length
        If iRow = 1 Then
            iCol = iCol + 1
            ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Scripts"
        End If
        iRow = iRow + 1
        ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = hText.Scripts.Item(tagIdx - 1).outerHTML
    Next
    
End Sub

Once start execution, code fetch data from website using the “msxml2.xmlhttp” functionality and put the content in ‘htmlfile’ object. With the function ‘getElementsByTagName’ the HTML tags can be parsed and the data embedded in those tags can be extracted. Similarly, the above code will also fetch “href” links to other web pages and scripts running in that page.

HTML Tags – Search Engine Optimization(SEO) Solutions

Though Computers can understand only keywords, Experts in Google, BING and other Search Engine designers are making intelligent systems that can rank a webpage based on its true purpose and just not only with too many searchable keywords by scraping keywords.

The worst part is, if we attempt to populate too many computer friendly keywords, then it is more likely that people who actually read your content might end up disliking it. Search Engines these days consider the user satisfaction level also in their ranking logic. To succeed, a publisher has to balance both (Computer Vs Human).

Web And Keyword Scrape for SEO

If there are only few pages, then the above points can be verified manually. What if there are 50 or 100 or even more pages? Use few available SEO page testing tools or develop your own tool with VBA web scraping explained below.

Sometimes SEO plugins (of course, in free editions) might pose few limitations (like only 5 Articles for SEO Review per month, etc.,), creating our own tool will come as handy to overcome these situations and we can also do the required customization as per our need.

This VBA code will make Excel retrieve data from website (scraping keywords from Web), parse the HTML tags in it and list down the below mentioned web analytic data points.

• Headers (H1, H2, and so on till H6)
• Text that are Bold
• HREF link to Internal/External web pages
• Scripts present in the page
• Occurrences of Keywords in Content

This is something that every Blogger has to verify in any of his article to ensure that webpage SEO task is complete.

Tools for SEO: Web Scraping VBA Code

If you are preparing a blog/article, you would be having a few keywords from Google Keyword Planner that are related to the keywords. For example, consider this post. which explains about a messenger option.

Article: How to Activate Whatsapp Voice call feature?

By reading the header of this article, reader and computer, both should understand -what the topic is all about. Always prepare a list of keywords about what you are going to explain; then prepare content with clear explanations. In my topic I chose ‘Whatsapp’ & ‘Voice Call Feature’ as focus keywords.

If these keywords appear in any of the headers and my content explains concepts with these words, then search engines will index my page for those words. That means it assumes that My page explains something about Whatsapp and its features, so bookmark these pages and display in search result if someone searches for these words.

Download Keyword Scraping Excel Downloaded 708 Times

So, to improve the importance of these words, put them in Headers and bold it in appropriate places inside the content. That’s it. You now know how to do a basic level of SEO for a website. But, take much care about number of times you are repeating the keywords, if it occurs too often, then Search engines will mark it as spam and your page rank will go down.

How to Scrap a Website Keywords for SEO?

We can now verify whether our topic has proper headers, important keywords are placed in proper tags and repeated appropriately, all links going out of webpage are not spam and any scripts are making unwanted execution.

This tool can report you how many times the keyword is repeated, but not whether it is valid or not, because every search engine has its own criteria on this allowed number of repetition for a keyword. Try a research on that and modify your article accordingly.

It is possible that most of us use many plug-in for our WordPress blogs and few might be inserting links to refer the developer websites. With the above code, we will get to know what are the external links out webpage is referring to and we take appropriate action based on the results.