Create Excel Price Alert E-mail Notification

This is going to be a pretty long and probably a bit intimidating tutorial, I know, but it pays off if it’s helpful to you.

I’ve been meaning to buy a mouse from a local e-commerce platform. The mouse isn’t expensive but I’d love to get it at a cheaper price, so I need to check for my mouse price daily and decide when to buy it. Amazon sells it 10$ cheaper than this e-commerce site.

Why check the price daily (manually) when I can have Excel do that? It can register prices daily in a table, compare last 2 entries and send me an e-mail if they are different (price from today is different than price from yesterday). This might look a bit like overwork for one simple price, but this can be adapted for more complicated data extraction (web scraping).

A small amount of knowledge of HTML and VBA languages would help, but it’s not necessary. I will try and explain everything as best as I can.

This tutorial will have 4 sections:

A. Locating the price in the website’s HTML source page

B. Installing prerequisites for our web scraping operation

C. Writing a code that reads the price and stores it in an Excel cell

D. Writing a code that stores daily prices into an Excel table, then sends e-mail if price changes

 

A. Locating the price in the website’s HTML source page

Open the page with the price and find it in that page. I am using Chrome, but you can use any browser – go to the desired page, check the price, then right click the page and select View Page Source or something similar.

As an example we’re going to take an amazon page for the mouse I want to buy which today is $39.89: https://www.amazon.com/Logitech-G502-Performance-Gaming-Mouse/dp/B07GBZ4Q68/

Now I search for that price in the page source and $39.89 appears 13 times. I am going to look for a class or an id element that precedes the price and is unique if possible. There is a variety of elements we can address in the page, but I’ve found that class or id are usually unique elements so they’re the easiest to target.

Here are all the individual elements you use in VBA to extract data from a HTML page source:

You will notice that  there are the same FindElements, plural, those are to scrape tables for example, or when you want to extract data with multiple elements – lists perhaps etc.

In our case, let’s look for 39.89 and find some unique element – class, id – that appears before it:

1st instance of 39.89 shows: <input type="hidden" id="attach-base-product-price" value="39.89" /> – no class here, but we have an id

Now let’s look for the id in the source code and see how many times it appears.

We’re in luck, the id attach-base-product-price is unique in the page source, so we are going to address it to extract our price.

If it would appear a few times we could still use it by providing an item number at the end of the code line – you’ll see below at C.

 

B. Installing prerequisites for our web scraping operation

In order for data extraction to work with new browsers instead of Internet Explorer which is no longer updated (since Edge), you will need to install Selenium libraries and a WebDriver. I will be using Chrome, but feel free to use any other you like: Opera, Firefox, Edge etc.

B.1. Install Selenium

• Download the latest Selenium Basic setup file from here: https://github.com/florentbr/SeleniumBasic/releases
• After download, start the executable, and click Next – I accept… – Next
• You will be provided with a list of components – instead of full installation choose compact installation that leaves only first 3 components selected (webdrivers in this pack might not be at their latest update and we need the latest which we’ll install in B.2)
• Click Next – remember the location where Selenium will be installed (you’re gonna need it in B.2), then Install and Finish

B.2. Install the latest ChromeDriver for Selenium

Here’s a page with browser drivers in case you won’t be using Chrome: https://www.selenium.dev/documentation/webdriver/getting_started/install_drivers/

I will be using Chrome, so here goes Chrome web driver installation:

• Check your Chrome version by clicking the 3 dots on the top right, going to Help – About Google Chrome. My version is 101.0.4951.41
• Download the latest stable driver version corresponding to your browser’s version from here: https://chromedriver.chromium.org/
• Extract the chromedriver.exe file from the archive into the SeleniumBasic folder from B.1, usually %UserProfile%\AppData\Local\SeleniumBasic

Now we are set to go writing our code to extract the price by using the unique id we found before.

 

C. Writing a code that reads that price and stores it in an Excel cell

Of course you won’t have to write this code yourself, just adapt the code below according to your needs. I will explain what the lines of code do in green comments.

You can do much more with VBA to extract data from tables, click buttons, insert usernames and passwords, download files etc., so make sure you learn some if you need it.

Let’s remember the 1st encounter of our price in the webpage we took as example: <input type="hidden" id="attach-base-product-price" value="39.89" />

We have our unique id and its value = 39.89 – the price we are looking for.

• Open an Excel file, go to Visual Basic Editor (you can go to VBE by right clicking a sheet, and clicking View Code)
• Right click in the tree project explorer to the left and choose Insert new Module (if there’s no tree explorer on the left, click on View – Project Explorer, then View – Properties Window)

Tree project explorer with your new module should look like this (without the Forms folder):

• While in Module1, Go to Tools – References – look for Selenium Type Library and check it, then OK to close References
• Paste the code below in that module – this is a simple example code that extracts the price and enters it in a cell. The we are going to use to store the prices in Excel and send us E-mail if price changes is a bit more complex.

Sub Get_Price()

Dim ch As New Selenium.ChromeDriver ‘ declaring the driver we are going to use to access the Chrome browser

ch.AddArgument “–headless” ‘ use this to stop Chrome window from showing, comment it to see the window open and go to the website

ch.Start “Chrome”, “https://www.amazon.com/Logitech-G502-Performance-Gaming-Mouse/dp/B07GBZ4Q68/” ‘ starting Chrome and navigating to the desired website
ch.Get “/” ‘ bringing the Chrome session into Excel

Dim el As Selenium.WebElement ‘ declaring our element
Set el = ch.FindElementById(“attach-base-product-price”) ‘ finding our element by the unique ID attach-base-product-price

‘ if the ID would have appeared a few times – let’s say 6 – we could still address one of them by adding an item number between parenthesis _

at the end of the line above, starting from zero: first occurrence = item 0, second occurrence = 1, below it’s third occurrence:

‘ Set el = ch.FindElementById(“attach-base-product-price”)(2)

Dim ws as Worksheet ‘ declaring a worksheet variable so we don’t have to write the longer address below every time, instead we write the word ws

Set ws = ActiveWorkbook.Worksheets(“Sheet2”)

ws.Range(“A2”) = el.Attribute(“value”) ‘ displaying the value (price) related to our ID in a cell

ws.Range(“B2”) = Format(Now, “dd.mm.yyyy”) ‘ insterting today’s date

End Sub

• I set the correct formatting for Ranges A2 (currency) and B2 (date) so here’s the result:

 

D. Writing a code that stores daily prices into an Excel table, then sends us e-mail if price changes

I will be using CDO (Collaboration Data Objects) method to send myself an e-mail without the need of an e-mail client like Outlook (even though I have it installed).

There is a security issue here since you will have to insert your e-mail address and password into the VBA code. But you can create a simple Gmail account just for e-mail alerts, this way if someone sees your password in VBA (has access to your file) they’re not getting sensitive data.

According to one Excel VBA Guru named Ron de Bruin, here are the advantages of using the CDO instead of Outlook or other e-mail client:

1: It doesn’t matter what Mail program you are using (CDO only uses the SMTP server).
2: It doesn’t matter what Office version you are using (97…2016, VBA code might be different for older versions)
3: You can send a range/sheet in the body of the mail (some mail programs can’t do this)
4: You can send any file you like (Word, PDF, PowerPoint, TXT files, …)
5: No Security warnings, really great if you are sending a lot of mails in a loop.

I’m going to use the CDO method in this tutorial. Now the code for e-mail sending will be a bit long, but you just need to adapt it to your needs. I will explain what each line of code does.

This section is going to be divided in 3 major parts:

D.1. Code for extracting data from the website and recording it in an Excel table in consecutive cells

D.2. Code for price comparing and E-mail alert

D.3. Scheduling daily check and e-mail sending operations

D.1. Code for extracting data from the website and recording it in an Excel table in consecutive cells

The code we did at C. does most of the job, but we need to insert data in the table every day and add up to the data from yesterday. So tomorrow, the table in the picture above should show the price in A3 and tomorrow’s date in B3, and so on, keeping the previous prices.

For that we will have to add a code that before extracting data will count the number of rows and then insert the data in the last row + 1 (first empty row). I will add the code above again, but without all the previous comments, and make newly added lines or modified code in color and add comments to them if needed:

Sub Get_Price()

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets(“Sheet2”)

Dim ch As New Selenium.ChromeDriver
ch.AddArgument “–headless”
ch.Start
ch.Get “https://www.amazon.com/Logitech-G502-Performance-Gaming-Mouse/dp/B07GBZ4Q68/”

Dim el As Selenium.WebElement
Set el = ch.FindElementById(“attach-base-product-price”)

Dim LastRow As Long
LastRow = ActiveWorkbook.Worksheets(“Sheet2”).Cells(Rows.Count, “A”).End(xlUp).Row ‘ this counts the rows in column A that are not empty

ActiveWorkbook.Worksheets(“Sheet2”).Range(“A” & LastRow + 1) = el.Attribute(“value”) ‘ LastRow is the last row of data, so LastRow + 1 is the first empty cell
ActiveWorkbook.Worksheets(“Sheet2”).Range(“B” & LastRow + 1) = Format(Now, “dd.mm.yyyy”)

ActiveWorkbook.Save

End Sub

Now running the above code a few times we’ll get the 39.89 price in multiple cells in our table. This is proof the code works fine.

We can clear the duplicate values now and leave only the first one A2:B2.

D.2. Code for price comparing and E-mail alert

Some of the lines below will have no explaining comments because their value shouldn’t change. They’re things related to CDO methods and its VBA code.

In order for this to work with Gmail, you will have to do 2 things from your Gmail settings. This way it doesn’t require you to insert some kind of sms received code for sending the e-mails. And they are:

1. Enable the “Less Secure” option for GMail – https://www.google.com/settings/security/lesssecureapps

This allows the e-mail sending utility to be accessed by Excel

2. Disable “2-step verification” – https://myaccount.google.com/security?hl=en

In order to test the price compare and e-mail sending codes, I manually modified the price from A2 to something greater than $39,89, let’s say to $43,29. The prices are different now, so the code will send me an e-mail. I use a gmail account to send myself an e-mail to my alerts yahoo account. Below is the updated Excel table and the e-mail I received.

Of course the e-mail can be made to look much better, depending on the alerts you send. At work I have an alert set that sends me an e-mail containing a table with various alerts shown in various colors. You just have to learn some HTML.

Below are the two additional lines that must be inserted at the beginning of your code, right after Sub Check_Price(). Their purpose is to prevent screen updating and alerts in order for the code to run faster.

Application.DisplayAlerts = False ‘ don’t display alerts like: “a code is trying to access you e-mail” or something similar
Application.ScreenUpdating = False ‘ don’t update the screen until the code has finished to run

 

Now here’s the code you have to insert between the ActiveWorkbook.Save and End Sub lines:

‘ today’s vs yesterday’s price compare and e-mail alert if different

Dim iMsg As Object ‘ e-mail message
Dim iConf As Object ‘ e-mail configuration
Dim Flds As Variant ‘ e-mail configuration fields
Dim msg As String ‘ the part of the string that says if today’s price is larger or smaller than yesterday’s
Dim dif As Double ‘ the result of the difference between today’s price and yesteday’s
Const cdoNTLM = 1 ‘ CDO Source Defaults

LastRow = ws.Cells(Rows.Count, “A”).End(xlUp).Row ‘ we count the new last row, after inserting today’s data

If Not (IsNumeric(Range(“A” & LastRow – 1).Value)) Then ‘ if value from yesterday is not a number
Exit Sub ‘ terminate code
Else

If Range(“A” & LastRow).Value = Range(“A” & LastRow – 1).Value Then ‘ if value from yesterday = value from today
Exit Sub ‘ terminate code

Else
If Range(“A” & LastRow).Value > Range(“A” & LastRow – 1).Value Then ‘ if value from today is larger than value from yesterday
msg = ” <font color=red>more</font> ” ‘ if today’s price is larger than yesterday’s store the word “more”
dif = Range(“A” & LastRow).Value – Range(“A” & LastRow – 1).Value ‘ today’s price minus yesterday’s
dif = Round(dif, 2) ‘ show only 2 decimals

Else
msg = ” <font color=blue>less</font> ” ‘ if today’s price is smaller than yesterday’s store the word “less”
dif = Range(“A” & LastRow – 1).Value – Range(“A” & LastRow).Value ‘ yesterday’s price minus today’s
dif = Round(dif, 2) ‘ show only 2 decimals
End If

Set iConf = CreateObject(“CDO.Configuration”) ‘ e-mail configuration
iConf.Load -1 ‘ CDO Source Defaults
Set Flds = iConf.Fields ‘ e-mail configuration fields

With Flds ‘ e-mail configuration options, aside from e-mail and password nothing should be changed
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “YOUR_EMAIL“ ‘ your e-mail goes here
.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “YOUR_EMAIL_PASSWORD“ ‘ your e-mail’s password goes here
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
.Update
End With

Set iMsg = CreateObject(“CDO.Message”) ‘ e-mail message

With iMsg
Set .Configuration = iConf ‘ e-mail configuration added
.To = “DESTINATION_EMAIL” ‘ destination e-mail, you can send it to yourself
.cc = “COPY_EMAIL” ‘ send a copy to another of your e-mails, or someone else’s
.BCC = “HIDDEN_RECIPIENT” ‘ if you want to send a copy to a hidden recipient
.From = “””Name”” <FROM_EMAIL_ADDRESS>” ‘ e-mail address from To and name
.Subject = “THE PRICE OF THE MOUSE HAS CHANGED” ‘ subject of your e-mail

‘ the body of the e-mail contains some HTML tags so that the text can be shown with formatting (bold, color etc.)
.HTMLBody = “<body style=font-size:16pt;font-family:Arial>In ” & _
Range(“B” & LastRow – 1).Value & _
” the price was $” & _
Range(“A” & LastRow – 1).Value & _
“. <br></br>” & _
“Today the price is $” & _
Range(“A” & LastRow).Value & _
” with $” & _
dif & _
” ” & _
msg & _
” than yesterday.</body>”
.Send
End With

End If
End If

Set iMsg = Nothing ‘ clear iMsg content
Set iConf = Nothing ‘ clear iConf content
Set Flds = Nothing ‘ clear Flds content

Application.DisplayAlerts = True ‘ enable alerts back
Application.ScreenUpdating = True ‘ enable screen updating since the code is done

 

Finally, your full code should look something like this:

 

D.3. Scheduling daily check and e-mail sending operations

In order to make scheduled tasks I prefer using a free software called RoboIntern instead of the Windows Task Scheduler (I’ve had some problems with tasks not running as they should have).

Download and install RoboIntern from: https://robointern.tech/download.html

Then you’re going to need to make 2 more files in the same folder as your Price alert.xlsm Excel file, for example in D:\PRICE ALERT.

 

1. Price alert.vbs file containing the following 9 lines:

Dim args, objExcel

Set args = WScript.Arguments
Set objExcel = CreateObject(“Excel.Application”)

objExcel.Workbooks.Open args(0)
objExcel.Visible = False

objExcel.Run “Check_Price”

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

Replace Check_Price with the name of your macro from Excel VBA.

 

2. Price alert.bat file containing the following 2 lines:

cscript “D:\PRICE ALERT\Price alert.vbs” “D:\PRICE ALERT\Price alert.xlsm”
exit

Add the paths to your .vbs and .xlsm files.

 

After the 2 files are created, in RoboIntern go to File – New task.

Under Actions tab:

Click the + button, select Run / stop a program – Run a program

In the Program path field add the path of the .bat file from point 2 above, something like: D:\PRICE ALERT\Price alert.bat

Click Add action

Under Triggers tab:

Click the + button, select Time – On time

Under Recurrence select Daily

Under Start scheduling on set the date for today,

Under Repeat every ___ days, set the desired interval – I set 1 day.

Then set desired hour of the day when you want your macro to run – an hour when your computer is on.

Under Options tab:

You can select further options, including a name for your task. I set to Execute actions Serially and Task Scheduling to Active.

 

Save your task. Run your task manually to check if it works.