Chase Anderson
VBA Final Project Write-up
Executive Summary
Having
struggled over the course of the semester to think of a useful VBA project that
I could use not only in the near future, but long term, and also one that would
be able to make me money was difficult.
Recently I spoke with my brother in law who was buying high priced items
for cheap at garage sales, Craigslist, and KSL, and would then turn around and
sell them for a much higher price on Amazon.
I thought it was an interesting business model, but nothing people
haven’t been doing since the dawn of mankind.
However, after learning so much in this class, the simple idea popped
into my head that I could automate much of his search with VBA. It would be incredibly convenient to simply
enter a search term and have excel do the searching for me.
For this project I wanted to complete two tasks:
1)
Search the local Craigslist website with a
specific item description, resulting in an autopopulated spreadsheet within
Excel showing the listings, prices, locations, and website addresses of each
listing.
2)
Turn around and search Amazon.com for the same
item description and return the price on Amazon and the URL for the item in
Amazon. The program will then calculate
the price difference for each listing compared to Amazon and give the profit % amount
for each listing.
The program would
require a very detailed set of words for the item description due to the
possibility of the Craigslist and Amazon searches finding items to compare that
the user did not intend. Due to my
brother in law’s tendency to purchase only high value items for their high
margin returns, this should not be a huge issue because most items have
specific model numbers/names or serial numbers that are listed on the
advertised listing.
In the end I was
able to code the program to near perfection.
On the first tab of the program, it has a simple button to click if the
user would like to search an item. Once
this button is clicked, it brings up a userform which accepts the item
description. With two clicks of the
enter key or clicking the search button with the mouse, the program begins to
run. While it is searching, the program
creates a new tab named after the given item description to be searched. The search results will be on the new
tab. Once it is completed search, it
shows the list of items, the description on Craigslist, the date of each
listing, the URL for each, the location, and the price on Craigslist. It then lists the Amazon price, the delta
between the prices, the potential profit %, and the Amazon URL.
No comments:
Post a Comment