Workflow to generate List of URL to be downloaded

[StockMarket]

Hello

 

I need to create a list of URL's to be downloaded from a server.The URL follow the exact same structure on a daily basis, only the DATE PORTION of the URL changes and everything else remains same for the specific type of file.

For making it clear, I am giving 3 different file examples for the Month of Jan 2019. I have to download many more files from that server on a daily basis, which have few different variations in the file names. But you will get the basic idea from these 3 examples -

Example 1

https://www1.nseindia.com/archives/equities/bhavcopy/pr/PR010119.zip
https://www1.nseindia.com/archives/equities/bhavcopy/pr/PR020119.zip
https://www1.nseindia.com/archives/equities/bhavcopy/pr/PR030119.zip
https://www1.nseindia.com/archives/equities/bhavcopy/pr/PR040119.zip
https://www1.nseindia.com/archives/equities/bhavcopy/pr/PR050119.zip

Example 2

https://www1.nseindia.com/archives/equities/mto/MTO_01012019.DAT
https://www1.nseindia.com/archives/equities/mto/MTO_02012019.DAT
https://www1.nseindia.com/archives/equities/mto/MTO_03012019.DAT
https://www1.nseindia.com/archives/equities/mto/MTO_04012019.DAT
https://www1.nseindia.com/archives/equities/mto/MTO_05012019.DAT

Example 3

https://www1.nseindia.com/content/historical/EQUITIES/2019/JAN/cm01JAN2019bhav.csv.zip
https://www1.nseindia.com/content/historical/EQUITIES/2019/JAN/cm02JAN2019bhav.csv.zip
https://www1.nseindia.com/content/historical/EQUITIES/2019/JAN/cm03JAN2019bhav.csv.zip
https://www1.nseindia.com/content/historical/EQUITIES/2019/JAN/cm04JAN2019bhav.csv.zip
https://www1.nseindia.com/content/historical/EQUITIES/2019/JAN/cm05JAN2019bhav.csv.zip

This example 3 given above is a bit different from the first two, because in this we need to add the YEAR and MMM Month portions separately to the URL.

I have to create such URL links for the past 10 years, following the URL Structure as explained in the above 3 examples. Can someone please suggest how to do this in Alteryx?

I have attached the Excel sheet having the required links for a complete month, as an example

Please note that the actual download files on that server are available only for those dates on which the Stock Markets were open. For the holidays, no files are available from the server, as I have mentioned in the attached sample file.

Thanks a lot

[Tyro_abc]

Hi 

I did it for last URL, please check the attached workflow. Similarly it can be done for other two types.  I only took date from Jan-01-2021 till today, you can increase the date range in Generate Row tool.

Regards

Arundhuti

[Tyro_abc]

version 2 of the workflow has all 3 types, please find attached

[danilang]

Hi @StockMarket 

To generate only the dates when the stock market is open, you'll need to implement the logic from this post into @Tyro_abc's solution.  My answer on that post also excludes holidays which are relevant in your case.   

Dan

[StockMarket]

First of all, I am sorry for my late reply. I got involved into some issues, so could not log back in to checkout the replies.

Thank you so much for the replies @Tyro_abc and @danilang 

I am not very familiar with Alteryx yet, so I am trying to make out the sense of the individual steps, one by one, that have been provided in @Tyro_abc  Solution.

First of all you have used the Text Tool to convert the URL structure in such a way that the "DATE Portion" of the URL's can be changed by using a formula, for example, the original URL of -

https://www1.nseindia.com/content/historical/EQUITIES/2019/JAN/cm01JAN2019bhav.csv.zip

will get converted to this URL type -
https://www1.nseindia.com/content/historical/EQUITIES/<YEAR>/<MON_NAME>/cm<DAY><MON_NAME><YEAR>bhav....

Then in second step, you have used a Generate Rows tool, with this formula -

[Date] > DateTimeAdd(DateTimeNow(),-80,"Days")

Which will generate the URL for the past 80 days.

Then as the third step, you  have used the Formula tool and defined the formulas as shown in the snapshot.

 

Now my problem with this solution is that I want to make use of the "DATE COLUMN" given in my Original Post. Because I will put there the list of only those days when the Stock Markets were open, so that I can successfully download the files for all those dates. In your method, it is creating the URL for every single date instead.

Secondly, I have to replicate this method for generating a lot of different URL's for different file types. So what exact modifications do I need to make for that, for every single new file types, which I will be adding, going ahead.

And lastly, I am not being able to figure out the exact reason for this, but none of your URL are actually working as a download link. All of them are showing errors, when I try to download them. Whereas the 3 example URL that I have given in the original post, are all working perfectly.

Can you please try to generate the URL for the dates from 1 Jan 2019 to 5 Jan 2019 and then compare them with my example URL to figure out, why your URL are not working?

If there is any possibility of any easier method which will make use of the dates input by me and which generates the corresponding URL for a variety of different file types, then please share some ideas. I will have to run this workflow on a daily basis for downloading the latest date files, every evening, after I have downloaded the initial files for the last few years.

Thanks and regards

PS: @danilang Thanks for sharing the method which incorporates the Holidays solution.

[afv2688 · Accepted solution]

Hello @StockMarket ,

I have finished a solution where you would only need to import the dates and the url would be generated.

If you check your links you will see I have replaced the strings to be modified with the data to be inputted.

You can add more strings if needed.

Regards

[Tyro_abc]

hi

Main part of that solution is the formula part.  I just took some random dates as was not sure which date you want to use, so kept it generic.  I generated for 80 days, you can generate it for 8000 days just by changing the parameter in "Generate Row" tool.  But if you have dates already, you can use them directly with your URL formats.  Use an "Append Tool" to join  URL format with specified dates.  Then use the same formula as I used. 

URLs are not working because of the date range I took. I tested with 2019 and 2018 dates, URLS are working. Workflow attached.

Method I used is generic in nature, so tomorrow if you have a new format, you just need to identify  fixed and variable portion of the URL and replace variable portion with formula.