Challenge #482: Company Share Price Analysis

[AYXAcademy]

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:

1. By what percentage has each company’s share price increased or decreased over the full 25-year period? (Use the Close column)
2. Which year saw the highest cumulative gain for each company?
3. What is the average gain or loss for each company by year-month?
4. Which months show the largest gains and losses for each company?
5. 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

Download Start File

Download Solution File

[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

[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:

  

Workflow:

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

[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

[Kenda]

Spoiler

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

[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

[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.