BLS PPI Database Query to Analyse Year-to-Year Change in Commodity Prices - by Graham Orme

EXECUTIVE SUMMARY

My internship this summer will be in the sourcing department for Union Pacific Railroad. I will most likely be assigned to be a sourcing analyst to purchase commodities or other materials that Union Pacific (UP) purchases to build track or run their operations. I reached out to a BYU MBA alum who works in the same department to see if I could do my final VBA project on something that could either benefit me or the department in purchasing materials. My friend mentioned that he and his department are continuously opening the Bureau of Labor Statistics (BLS) website and pulling the Purchase Price Index (PPI) for various commodities to analyze and anticipate their price changes. Thus, was spawned my idea and you have this project before you; VBA code that automatically does exactly what my friend at UP does by hand every month.
The three commodities I am searching for in this project are sand/gravel aggregates for cement, cement itself, and concrete. All of which go into the foundation of UP’s train tracks. At the push of a button my code accesses the PPI database for each commodity, one by one, on the BLS site and loads them onto three tabs in a prepared worksheet. Two input boxes then pop up asking for the month and year of the price one would like to see from the index and loads that price and the price from exactly a year prior onto a table that calculates the percentage change in price from year to year. At the same time, the projected price for the subsequent month of that which was selected, along with the projected price of the commodity exactly a year from then is put into another table below to calculate the projected change in price.

WRITE UP
VBA CODE