Overview
You are an investor that likes to invest in individual stocks. The “Portfolio” sheet contains all your investment purchases from February 2013 - January 2015 and the subsequent sales from February 2015 - January 2017.
You kept track of the stock tickers you invested in, as well as when you purchased and sold them. However, you didn’t keep track of the stock prices when you purchased and sold the stocks. You purchase a new security every week and hold it for about two years. You also don’t keep track of the company names, just the tickers. Sometimes the stock market is closed on the days you would like to schedule your securities purchases (“Desired Purchase Date”), so your brokerage service schedules the purchase at the next available trading date, but never before your desired purchase date. Your purchases are always scheduled to finalize at market open, so you always make the purchase at the market open price. You like to hold the stocks for just under 2 years. Like with your stock purchases, sometimes the market isn’t open when you’d like to sell (“Desired Sale Date”), so your brokerage service schedules the sale for the next available trading date. You always schedule the sale at market close, so you always receive the closing price for the stocks that you sell.
You were able to find stock data for 2013-2018, and the company names for most of the tickers in that data.
Instructions
Complete the following:
Populate the “Company Name” column on the “Portfolio” worksheet by using XLOOKUP() to find the company name on the “Company_Names” worksheet. Remember that you found most of the company names. If a company name isn’t in the “Company_Names” worksheet, return the words “Company Name Unavailable”
To find the price information for your purchases and sales, you’ll need to use XLOOKUP() to find prices. A unique identifier will be needed to find the correct prices for a given ticker on a given day.
After creating the unique identifier, use the XLOOKUP() function to retrieve the stock price for when you purchase and sell each stock. These prices will go in the “Purchase Price” and “Sale Price” columns of the “Portfolio” worksheet.
You can use the CONCAT() function in the “Unique ID” column of the “5_Yr_Stock_Price_Data_2013-2018” worksheet to create a unique identifier. Some users may find it helpful to create “Unique ID” column(s) in the “Portfolio” worksheet. Alternatively, you can create the unique identifier exclusively in the XLOOKUP() functions. Under this alternative, there is no need for adding data to the “Unique ID” column or creating similar columns.
When selecting the data for the “Purchase price” column, make sure that you select every row in the “open” column of the “5_Yr_Stock_Price_Data_2013-2018” sheet. You can use the keyboard shortcut ctrl + end to find the last cell in a dataset.
This was great practice with xlookup and some of its additional arguments. Something I find helpful when working with multiple tabs in Excel is to open a new window so that I don’t have to jump between them when writing a formula. A keyboard shortcut for that is ALT + W + N
This challenge was super fun! There was lots of data to work with and yet things were really approachable. I enjoyed creating the unique identifier for each trading day and working that into my xlookups. It was also a good reminder to always check for the match type I want with my lookups to avoid null cells! I’ve attached a screenshot with the first few rows of my data with one of the longer formulas shown at the top.
What a fun challenge!! I wasn’t quite sure what the open/high/low/close tabs meant, but the suggestion got me taken care of. I couldn’t imagine going through this all by hand. I also found it useful to use the keyboard shortcut Ctrl +Pg Up/Dn to cycle through the pages. That helps me from wasting time trying to find my mouse again!
This was a great refresher on XLookups and integrating a few other functions! It took me a little bit of time to figure out the most efficient way to make the UniqueID but after using the Concat function it made everything else go smoothly!
Great challenge with XLOOKUP. I originally mixed up the order of my concat on the stock price data sheet which was throwing off some of my numbers. It was good to double check to make sure I had everything correct.
This was a good one! After seeing Prof Woods comment about not creating a unique field I challenged myself to create a similar solution without cross-referencing his. My goal was to have everything self-contained within one cell.
While I was successful, I think I would in the future just go ahead and create the key field. My method requires each cell generate the unique field for the entire array, and is thus very resource and time intensive.
Great practice for XLOOKUP, I never really get to use those optional parameters in the formula so it was fun to look at the data and how I could use those parameters to fill in those tricky N/A cells.
I added a color scale to my last column to really show those high and low gains/losses.
This was a nice little practice with XLookups! I’m not sure if other people had a few lines of data that couldn’t find the purchase/sell prices, but I edited the formula in the pink column to show “Not Applicable” in those instances instead of an error value.
This was a good practice. I will admit, I had to look at the solution video only for the very last part in trying to return values for purchases and sales that were made after the desired date. It was definitely nice to learn the function of the extra data inputs in the XLookup.
This was a great challenge! I am not too familiar with XLOOKUP, so it was very beneficial to explore the different parameters of this function. I used conditional formatting to format the losses red.
Initially, I expected the actual purchase date to be only a day or two after the desired purchase date. I had attempted a nested function to solve that problem. When the actual purchase date was years after the desired purchase date in some cases, I had to do some reading on the optional arguments within an xlookup. It was very informative!
