Web Scraping -

[msve]

Hello,

I am trying to retrieve "Current Monthly COFI" and "Current Semiannual Indices" rates from website - http://www.fhlbsf.com/resource-center/cofi/. I created attached workflow to pull Monthly COFI rates from the website using the download complete monthly history link "http://www.fhlbsf.com/resource-center/cofi/monthlyhistory_file.asp".

My question is

1 - Can I scrape the data with out using the link by directly looking for data on website?

2. Can I use the same workflow and add another link to pull Semiannual Indics (http://www.fhlbsf.com/resource-center/cofi/semimonthlyhistory_file.asp) 

[JordyMicheal · Accepted solution]

Hey there MSVE!

So yes, it is possible!!

Little bit of work to rework the data, but here you go - I've done it for you.

Text to columns a few times...

First to split on the new rows all the way down the second to split them on the tabs and get them into separate columns.
Little data cleanse to remove some white-space, drop the fields we don't need, and a dynamic rename to get the headers.

I've gone ahead and done both examples you've asked for (the only difference is the number of columns to split into).

Might I suggest you try to make a MACRO that asks for how many columns to split into and package this up for yourself?

Give it a shot!

[msve]

Hi Jordy,

Thank you so much for your response. Looks like the version I am using is the older one and I am unable to open the file you attached. 

[JordyMicheal]

Hey @msve 

Oh shoot - I'd update for the new features for yourself!

If you don't feel like it here are the steps:
Text Input - URL as a header with the URL below

Download tool - URL set to URL and download as string

Text to Columns - On "DownloadData" delimiters is \n and split to rows

Text to Columns - On "DownloadData" delimiters is \t and split to 3 columns (if you have more columns in the data ie the second example then the number is that)

Data Cleanse Tool - DownloadData1, 2, and 3 clean the white space

Select Tool - Drop all columns you don't need

Dynamic Rename Tool - Take Field names from the first row

That should get you what you need if it makes sense 🙂

[danilang]

Hi @msve 

Alteryx workflows and macros are just xml files.  Open the .yxmd in notepad and change the second line to match your version that you can find in Help->About

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2019.2">   Change the bold number here 
<Nodes>

Note.  don't try this with the .yxzp file.  It's actually a renamed zip file and not comprehensible to mere mortals.

Dan

[estherb47 · Accepted solution]

Here is a tip for downgrading yxzp files.

Change the .yxzp extension to .zip. Ignore the warnings

Open the zip files. Open each .yxmc and .yxmd within in notepad, and change the version per @danilang 's instructions above. Save each file and update the zip with the new files.

Close the zip file. Change the extension back to .yxzp. Ignore the warnings.

Now you can open in your version of Alteryx!!

Cheers,

Esther

[msve]

Thank you @JordyMicheal - Your instructions were to the point. Also, I am able to access the file by following @estherb47 instructions.

Thank you @estherb47 and @danilang  for your trick, that was really helpful.I am able to open the file 🙂

Love this community 🙂