Challenge #69: Web Stock Data

The solution to last week's challenge is HERE.

Weekly Challenge 70.jpg

Today’s challenge is for all our investors out there! Say you wanted to perform your own unique analyses on a stock, or at least have its trends handy for notification if it was hitting an established threshold of yours. How would you obtain this data?

For our challenge, we’ll be using Yahoo Finance! Use the URL below to gather useable (parsed; tabulated format) stock data on AYX (Alteryx!):

http://ichart.finance.yahoo.com/table.csv?s=AYX

As of May 2017 - the Yahoo API has closed its service. Instead please use the following URL to get historical data:
https://www.google.com/finance/historical?output=csv&q=AYX
*Due to differences in the API services - there will be no field in the resulting response for "Adj Close"

As of December 2017, the previously used URL produces an error. We've updated this Challenge to point to a new link to download a dataset on attributes of mushrooms. The Start File and Solution have been updated to reflect this change. Happy Challenging!

challenge_69_solution.yxmd

challenge_69_start_file.yxmd

Parse

Preparation

Advanced

Data Preparation

Basic

Accepted answers

All comments

patrick_digan

Spoiler

I went with download tool followed by some text to columns (\n to get rows and , to get columns), followed by dynamic rename to get the column headings. The auto-field gets converts the strings to doubles, and the filter gets the data through 4/28 to match the sample output

challenge_69_PD.yxmd

BenMoss

Learn some new things thanks to this one!

Spoiler

Challenge_69_BM.yxmd

MarqueeCrew

same as patrick

challenge_69_MarqueeCrew.yxmd

Joe_Mako

Spoiler

Only need two tools:
- Download URL to a Temporary File
- Dynamic Input configured to read in a comma separated CSV file, changing full path based on DownloadTempFile

challenge_69_Joe_Mako.yxmd

alex

I took it a little further to practice with iterative macros and provided a 50 day moving average and 52 week high and low for each day. Volume is a bit skewed since the first day of trading was much larger than normal, but that will auto correct once there are more than 50 days in the data set.

Spoiler

challenge 69 Macro

Macro

results

challenge_69_AC.yxzp

alex

@Joe_Mako- Nice solution. Took me a bit to figure out the configuration of the second tool, but I got it to work.

NicoleJohnson

My solution - just for kicks, I went a bit of a different route and created an app.

I figured if you had this data and you were looking for certain criteria (such as Adj Close price changed by more than +/- 5% from the prior day, or if it reached a price greater than or less than a certain value), you would want to know. So I added a Daily Change % calculation and then some app parameters. Set it up to just look at "yesterday's" results (i.e. last Friday if today is Monday, otherwise previous day). This would be easy enough to add another prompt asking for the stock symbol, which could then update the text input accordingly and get you results for any stock...

Spoiler

challenge_69_NicoleJohnson.yxwz

MarqueeCrew

@NicoleJohnson,

Ok, go the extra mile. Try the yahoo finance API and get some real data! I like your approach and encourage you to use an API to deliver value.

Cheers,

Mark

DanG_dup_78

I don't know the answer, but I'd definitely like to know what it is and then learn how to build it in Alteryx Designer for myself. Thanks!

Phil_L57

Nice use of the download tool. The output isn't identical as there are now more days in the yahoo data

challenge_69_start_file vPL.yxmd

jamielaird

This was a nice place to start for my first weekly challenge.

A pretty simple solution to a simple challenge, with a couple of charts thrown in as a bonus:

Spoiler

Alteryx Weekly Challenge 69 Jamie Laird.yxmd

jamielaird

I looked around for some guidance on how to tag my image as a spoiler but couldn't find any - what should I have done?

Natasha

My solution is similar to others, but used it as an opportunity to try reporting tools.

Spoiler

challenge_69_NK.yxmd

Simona

Definitely I learnt something new today! Thanks for this challenge.

Spoiler

I used first the download tool to extract the data and then the text to column, first to split the data by rows and then another to split it by columns. Then I just removed unused fields and used the dynamic rename to pick from the first line of data the column headers. Autofield to give a field type as it was all string. Fun!!

JoshK_dup_167

Went above and beyond on this one. Created an app where you can input (probably) any real ticker and it will spit out the data in csv format as well as 2 charts for your viewing pleasure (saved to same folder as the app location). Any feedback on where this can be improved is welcome!

challenge_69_my_app.yxwz

SeanAdams

Wow - you turn you back for 1 min, and then @JoeM posts the next challenge while you're not looking, and before you know it there's dozens of bright people who have solved the problem already.

So - summary is that I did much the same as @patrick_digan @BenMoss @MarqueeCrew and @alex 's basic solution.

I gotta say, people really swung for the fence on this one with charts, and graphs etc - I just just used some of the analytical tools (violin plot; histogram and a scatter plot) to see if there are any obvious anomalies (and there are :-))

was impressed by @Joe_Mako 's approach - in retrospect it's obvious, but the trick is seeing it the first time.

Spoiler

I also struggled with the newline use of TextToColumns so did the same workaround as @NicoleJohnson :-) But peeping at the solutions above, now I know this one for the future...

challenge_69_SeanSolution.yxmd

JoeM

I'd love to take credit for this challenge @SeanAdams, but this week @MattD started his weekly challenge authoring campaign! He's got some good ones planned for you guys over the next few weeks!!!

MattD

Here's a solution!

Spoiler

estherb47

Well, the yahoo link doesn't seem to be working for me today, so I connected through Yahoo Finance and downloaded the HTML.

A bit of cleaning, a bit of RegEx, and voila! A useable table. When I can actually connect to the API, I will post a new solution.

Spoiler

challenge_69_EHB_solution.yxmd

sehengratis

I totally agree with you DanG

a-ok-emoji-arancho-doc-translation-localization-services.png

LordNeilLord

Hey @MattD,

The Yahoo link is dead for this challenge!

Any chance you could supply an alternative?

JoeM

It's come to my attention that the Yahoo Finance API closed down. Instead, I've updated the original post to use a call to the Google Finance API. The differences between the APIs are minimal and you can expect one less field from the Google API.

Get back to it @LordNeilLord!

LordNeilLord

Nice and straight forward approach

Spoiler

Thanks for updating the link @JoeM

@SeanAdams 71 down, 3 to go!

challenge_69_LNL.yxmd

vishalgupta

challenge_69_Vishal.yxmd

A_Twa

Great challenge, I hadn't yet had an opportunity to use the download tool. Thanks!

challenge_69_start_file_AJT.yxmd

nick_ceneviva

Solution attached!

challenge_69_Ceneviva.yxmd

data_rachel

Spoiler

Had a play with the reporting tools

challenge_69_start_file_RP.yxmd

samjohnson

It might be interesting to see the daily change in stock price and the positive and negative 1-standard deviation of the average change over this time period.

challenge_69_samjohnson.yxmd

marcreid

Solution

Spoiler

challenge_69_MR.yxmd

dominiklz

Spoiler

challenge_69_Dominik.yxmd

ggruccio

Hi, is there an issue with the google link now? I'm seeing something like "but your computer or network may be sending automated queries" "To protect our users, we cant' process your request right now." I got frustrated so I tried one of the posted solutions and am getting the same message. There must be an issue because there have been no solutions posted recently... Was looking forward to this one! Hopefully we can get the URL issue resolved, thanks!

ChristineB

@ggruccio, thanks for reporting this! Let me see if I can find a similar dataset with a functional API ( was seeing the same as you; it appears the Google Finance API is no longer accessible). I'll post back when I've updated this Challenge.