108|ALTERYX – Stock it Up

BYU Student Author: @klayton
Reviewers: @Jae, @Mark, @Hyrum
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

Overview
You are a CPA working at mid-size, regional firm. Your responsibility is to process hundreds of people’s tax returns every year, including their 2022 tax returns. Many of your clients are wealthy and frequently trade stocks in the stock market. Today, your task is to determine whether each stock was held “long-term” or “short-term” using Alteryx. To be considered “long-term,” the stock must have been held for over a year.

The distinction between long- and short-term capital gains is significant. Long-term gains are taxed at a preferential rate, while short-term gains are taxed at the normal income tax rate. The difference in tax rates can be substantial and could potentially save your clients hundreds to thousands of dollars in taxes. As a tax planning opportunity, you will output a file that lists how many more days your client needed to hold onto each of their short-term stocks to qualify for long-term capital tax treatment.

Instructions

  1. The data provided includes all your client’s stock transactions. Calculate the total long- and short-term capital gain or loss.

    • The final .csv file should contain the following information:
      Challenge108_Image1
  2. For part 2, filter so you have only the short-term stocks.

    • Calculate how many days more the client would have needed to hold onto the stock in order to sell them as long-term assets instead of short term.
    • The final .csv file should contain the following information:
      Challenge108_Image2
      Professionals make sure to keep a record of their work. Don’t forget to output the files into a .csv or excel file for future use and record keeping.

Data Files

Suggestions and Hints
  • Make sure to change the data type on the date columns. DateTime formulas only work on Date data.
  • Reminder, in order to be long-term, the difference between the sale date and the purchase date must be greater than 1 year (365 days).
  • You are preparing 2022 returns. Any stocks sold outside of 2022 shouldn’t be included in the reports.

Solution

image
Good refresher on date formulas and tools!
Alteryx Sum it up.yxmd (16.4 KB)

1 Like

Great challenge! Hopefully, we can use this data to save the client money.
108.yxmd (16.3 KB)