Hi Community members, A solution to last week’s challenge can be found here. Thanks to James Bevan (@JBevan89) for submitting this week’s challenge! This week, you’re stepping into the role of a data analyst tasked with examining the historical share price performance of 14 companies over the past 25 years. Your mission? Extract key insights from this long-term dataset using your analytical skills. Here are your core questions to answer: * By what percentage has each company’s share price increased or decreased over the full 25-year period? (Use the Close column) * Which year saw the highest cumulative gain for each company? * What is the average gain or loss for each company by year-month? * Which months show the largest gains and losses for each company? * Using the Open, Close, High, and Low values, calculate the volatility for each company. Bonus Task: Go beyond the questions above. What additional trends or insights can you uncover? Could your workflow be optimized, perhaps by using a macro or other automation techniques? Once you have completed your challenge, include your solution file and a screenshot of your workflow as attachments to your comment. Good Luck! The Academy Team Source: https://www.kaggle.com/datasets/zongaobian/netflix-stock-data-and-key-affiliated-companies Challenge482_start_file.yxzp Challenge482_solution_file.yxzp

Challenge Completed Spoiler Challenge_482_Joe_Serpis.yxzp

Spoiler Challenge482_solution_file.yxmd

team solve Challenge482_start_file.yxzp

Challenge #482: Company Share Price Analysis

AYXAcademy

Full Width - WC banner.svg

Hi Community members,

A solution to last week’s challenge can be found here.

Thanks to James Bevan (@JBevan89) for submitting this week’s challenge!

This week, you’re stepping into the role of a data analyst tasked with examining the historical share price performance of 14 companies over the past 25 years. Your mission? Extract key insights from this long-term dataset using your analytical skills.

Here are your core questions to answer:

By what percentage has each company’s share price increased or decreased over the full 25-year period? (Use the Close column)
Which year saw the highest cumulative gain for each company?
What is the average gain or loss for each company by year-month?
Which months show the largest gains and losses for each company?
Using the Open, Close, High, and Low values, calculate the volatility for each company.

Bonus Task: Go beyond the questions above. What additional trends or insights can you uncover? Could your workflow be optimized, perhaps by using a macro or other automation techniques?

Once you have completed your challenge, include your solution file and a screenshot of your workflow as attachments to your comment.

Good Luck!

The Academy Team

Source: https://www.kaggle.com/datasets/zongaobian/netflix-stock-data-and-key-affiliated-companies

Challenge482_start_file.yxzp

Challenge482_solution_file.yxzp

Intermediate

Data Preparation

Core

Accepted answers

All comments

Pilsner

@AYXAcademy The data doesn't seem to download with the start file.

For anyone interested, I have been able to download the data from the Kaggle dataset link. It appears we need to input all the files from the Kaggle ZIP download.

BS_THE_ANALYST

@JBevan89
What's the formula for calculating volatility?

5) Using the Open, Close, High, and Low values, calculate the volatility for each company.

mmontgomery

C482

Spoiler

Part 5 was some googling, not sure if it's right but an attempt

c482.yxzp

Pilsner

Core! I struggled with this challenge 😂 Still not quite there with part 5, as I couldn't find the right formula for Volatility. I tried many options, including the

Garman-Klass formula, but in the end I decided to stick with a much more basic calculation. Very interested to see if others get part 5 to match exactly! Here's my solution:

Spoiler

Here's the formula I used for part 5. It doesn't use all 4 columns, but the answer was close:

100 * ([High] - [Low]) / [Open]

Part 5 answer:

482 (part 5).png

Workflow:

Time Taken : 1:08:45 (yes I did spend a while on part 5)

482 (Share Price).yxzp

JBevan89

Spoiler

There will be a few ways to get this, but what I did was take the variance (high minus low) and then divided by the low, to achieve the volatility, and then as an average. I should not be giving the answer away, but I think the question should include 'average' - as a hint

balajilolla2

Spoiler

Solution Attached

Spoiler

Challenge482_start_file.yxmd

Kenda

Spoiler

a few small rounding differences, but should match other than that

Challenge482_kenda.yxzp

Inputs.zip

olga_strubbe

Thank you, @JBevan89

It was a good practical challenge for volatility in stock prices.

Similar to other folks, I could not get the formula for #5 to match the output, but got very close. Could you please let us know what Volatility formula should be?

I used (([High] - [Low]) / [Low])* 100 for % Volatility - did not use Open or Close prices - I tried to incorporate all 4, but got further away from the output.

Please see my solution attached + screenshot. Thanks,

Spoiler

Challenge 482_OS.yxmd

JBevan89

Spoiler

Hi olga. In Question 2, I used a multi-row formula tool to calculate the gain. Because of this, with question 5, i linked the data from question 2, and so it picks up the data from the date of 1999-11-01, instead of 1999-10-29. So I guess one working day is causing the slight difference.

alineruizcampos

Like others, could not get the variance to match

Spoiler

Challenge482_Aline.yxmd

Bobbyt23

Lots going on in this one. Not sure on part 5!

Spoiler

Weekly Challenge 482 26-06-25.yxzp

DaisukeTsuchiya

I can't match the answer.

Spoiler

Challenge482_start_file_DT.yxmd

AkimasaKajitani

My solutioin!

Unfortunately, I don't know how to calculate the exact volatility value.

Spoiler

Challenge482_start_file_AK.yxzp

abacon

This one took some thinking, I don't think I got question 5 exactly right but this was fun.

Spoiler

Challenge482_abacon.yxzp

AnaKomissaroff

That was fun!
Here is my solution

Chanllenge_482_Solved_AK.yxmd

KLTerrell

Whew! THANK YOU @JBevan89 for adding the comment for the Q5 formula. The instructions to use Open, Close, High, and Low are misleading and I went down a way too long rabbit hole before checking that comment!

This was super fun. Thanks!

Spoiler

My file has my first (way more complicated .... I should say way *over-complicated* attempt at Part 2. I first read it as max day - min day instead of the difference across each day.

Alteryx Challenge 482.png

Alteryx_Challenge_482_Terrell.yxmd

RolandSchubert

Spoiler

Challenge482_solution_rsc.yxzp

ahsanaali

Spoiler

Challenge482_Solution_AAA.yxmd

RWvanLeeuwen

First, I'd like to mention that this type of challenge is much more approachable when KPIs are clearly defined.

Here's my take:

Spoiler

Challenge482_start_file - RWvL.yxmd

martinson

Having some issues getting the data, looking forward to getting it set up and having a crack

dkienstra

@PilsnerHow did you input the data from the ZIP-file whilst making the distinction between the several companies still possible. When I import the ZIP-file as a whole, the output for the File Name is 'Archive. See image below as a demonstration:

JGonzalez1015

My solution

Spoiler

Challenge482_JG.yxmd

myastarling

Here's my solution. I am not sure what formula I should have been using for volatility because I got dramatically different results - I used what I was taught in business school for annualized volatility which was the standard deviation of the difference in the closing price * the square root of the number of trading days. I wouldn't normally use these entire datasets to compare the volatility of the different stock and these are all capturing different time periods but I don't have enough information otherwise.

Challenge482_myastarling.yxmd

Yogesh1795

Answers are different from Output, maybe because of data

Challenge482 Solution.yxmd

RebekaMazuchova

Challenge482_RM.yxmd

Erin

I got 1-4 but it took me way too long just for those (series of rookie mistakes). I didn't attempt question 5. If Pilsner struggled, then I don't stand a change. 😂

challenge482_submission_EM.yxzp

SteveY02

Completely confused about Question 5. There are several methods to calculate volatility; I think the author needs to specify which formula we should use.

Challenge476_start_Solution_Steve.yxmd

Carolyn

Solved

Huge thank you to @Kenda for posting the Input files!

Spoiler

I had some rounding differences in #2 and #3.

For #5, I did 100 * ([High] - [Low]) / [Close], which got me close to the solution in the file

2025-07-03 10_31_40-Alteryx Designer x64 - Challenge482_Carolyn.yxmd.png