Downloading a website file, need help!

[JoelBC]

I am trying to schedule and automatic download of a file (Canadian Prime Interest Rates) from a website to a specific folder.

1. The website is https://wowa.ca/banks/prime-rates-canada

2. The file I am trying to download is the CSV file near the top of the page per the below image.

3. I created an Automator app but it fails. The Automator app can be found here .


4. I would appreciate help scheduling an automatic download of this file whether via Automator or another method / tool.


Thank in advance.

 

[DarkPremiumCho]

This is kinda complex. This CSV is not a conventional object that you could download from the website. This is the main reason why your Automator app is not working.

---

The chart contains 2 data sets, the overnight rates and the prime rates.

Your browser gets the raw data and draws the chart.

This request gets the overnight rates.

curl 'https://wowa.ca/api/canada-overnight-rate?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Accept-Encoding: gzip, deflate, br' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'tracestate: REDACTED' \
-H 'newrelic: BASE64_ENCODED_INFORMATION' \
-H 'Priority: u=3, i' \
-H 'traceparent: REDACTED'

This gets the prime rates.

curl 'https://wowa.ca/api/canada-prime-rates?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Accept-Encoding: gzip, deflate, br' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'tracestate: REDACTED' \
-H 'newrelic: BASE64_ENCODED_INFORMATION' \
-H 'Priority: u=3, i' \
-H 'traceparent: REDACTED'

The response from the server is in JSON format. This is a snippet:

[{
    "timestamp": -1104519600000,
    "Prime Rate": 5.5
}, {
    "timestamp": -1072983600000,
    "Prime Rate": 5.21
}]

The raw data is an array containing the timestamps (UNIX format) and the rates.

---

I noticed both requests sends certain trace headers. This means the website may block non-human requests, making it harder to automate.

If the website does not perform this check, it can be done by a script:

1. perform these 2 requests
2. convert the 2 JSON objects to CSV

If the website blocks non-human visits, you may have to use a headless browser and more scripting (JavaScript to be precise), but that's beyond the scope of my skill.

---

Also ensure the website ToS and the Canadian law do not forbid you to do that!

 

[JoelBC]

@DarkPremiumCho

As a start much thanks for the assist which is well beyond my skillset.

A few follows ups:

1. How do I run / test the code that you wrote bearing in mind that I would like to have it run every morning at 5:15 AM (i.e., is there a way to to do so with Automator). This is the reason I tried building a workflow with Automator as I could make it a calendar event.

2. The server response / snippet is not a problem as I can easily manage that through Power Query

3. Where / how do I specify the folder and file name to store the server response / snippet?

4. Will teh above code work for other websites by changing the url in the curl command?

Thank you.

Joel

 

[DarkPremiumCho]

Good news. The website does not block automatic scripts, at least not this one I used:

curl 'https://wowa.ca/api/canada-overnight-rate?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Priority: u=3, i' \
> ~/Downloads/canada-overnight-rate.json
# save to the Downloads folder of the current user, OVERWRITING any existing file with the same name

curl 'https://wowa.ca/api/canada-prime-rates?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Priority: u=3, i' \
> ~/Downloads/canada-prime-rates.json
# same as above

# MIT License

# Copyright (c) 2024 DarkPremiumCho
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

1. Save the code to a plain text file called getrates.sh and run bash /Path/to/getrates.sh in your Terminal.

I can't get it running using a calendar event. Says permission denied. You may have to use launchd.

2. The missing puzzle piece is converting the JSONs to a CSV. I prefer Python but I presume you can do that using Power Query.

3. Path and file name are customizable in the code.

4. Not really. They typically require some particular headers besides the URL.

 

[JoelBC]

@DarkPremiumCho

Again, ,such thanks for the help noting the following:

1. Appreciate the revised code which I was able to run successfully.

2. Automation will hopefully be possible using Automator

3. I am having problems getting Excel Power Query to open the resulting JSON files. When I try to one them I get the following message despite the fact that Excel can see the files as I pick / select them:

Any ideas?

Thank you.

 

[DarkPremiumCho]

I am not familiar with Excel but it looks like an unexpected suffix %EF%80%8D in the file path. Try trimming that. The %EF%80%8D may be displayed as  somewhere else.

 

[JoelBC]

I will give it a try and report back. Thank you.

 

[JoelBC]

I am not familiar with Excel but it looks like an unexpected suffix %EF%80%8D in the file path. Try trimming that. The %EF%80%8D may be displayed as  somewhere else.

I am having trouble trimming the file. Wondering whether you have any ideas / recommendations?

 

[DarkPremiumCho]

I meant trimming the file path, not the file. I assume you have to enter a path to the file when adding the data source to Power Query?

 

[JoelBC]

Appreciated and understood but that is not possible (at least I think not) as Power Query includes a file picker and select the file name in full. That said, we will investigate and revert back. Stay tuned.

UPDATE: I confirm that the file name cannot b trimmed when selecting the file in Power Query.

 

[LuisMD]

Good news. The website does not block automatic scripts, at least not this one I used:

curl 'https://wowa.ca/api/canada-overnight-rate?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Priority: u=3, i' \
> ~/Downloads/canada-overnight-rate.json
# save to the Downloads folder of the current user, OVERWRITING any existing file with the same name

curl 'https://wowa.ca/api/canada-prime-rates?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Priority: u=3, i' \
> ~/Downloads/canada-prime-rates.json
# same as above

# MIT License

# Copyright (c) 2024 DarkPremiumCho
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

1. Save the code to a plain text file called getrates.sh and run bash /Path/to/getrates.sh in your Terminal.

I can't get it running using a calendar event. Says permission denied. You may have to use launchd.

2. The missing puzzle piece is converting the JSONs to a CSV. I prefer Python but I presume you can do that using Power Query.

3. Path and file name are customizable in the code.

4. Not really. They typically require some particular headers besides the URL.

Thank you for this.

The method to solve for the "%EF%80%8D" error was to paste the code directly in the terminal (rather than pointing to a Shell script".

Now, we are at the point of Automation using the Automator in Mac.

When creating a terminal script in Automator, the file is now being downloaded with the "?" appended in the filename after the .json extension.

Any ideas on how to get around this?

EDIT: No help needed. We were able to solve the problem.

Thank you kindly,

DarkPremiumCho​

 

[LuisMD]

Good news. The website does not block automatic scripts, at least not this one I used:

curl 'https://wowa.ca/api/canada-overnight-rate?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Priority: u=3, i' \
> ~/Downloads/canada-overnight-rate.json
# save to the Downloads folder of the current user, OVERWRITING any existing file with the same name

curl 'https://wowa.ca/api/canada-prime-rates?recent=false' \
-X 'GET' \
-H 'Accept: application/json, text/plain, */*' \
-H 'Sec-Fetch-Site: same-origin' \
-H 'Accept-Language: en-CA,en;q=0.9' \
-H 'Sec-Fetch-Mode: cors' \
-H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/18.0.1 Safari/605.1.15' \
-H 'Referer: https://wowa.ca/banks/prime-rates-canada' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Priority: u=3, i' \
> ~/Downloads/canada-prime-rates.json
# same as above

# MIT License

# Copyright (c) 2024 DarkPremiumCho
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

1. Save the code to a plain text file called getrates.sh and run bash /Path/to/getrates.sh in your Terminal.

I can't get it running using a calendar event. Says permission denied. You may have to use launchd.

2. The missing puzzle piece is converting the JSONs to a CSV. I prefer Python but I presume you can do that using Power Query.

3. Path and file name are customizable in the code.

4. Not really. They typically require some particular headers besides the URL.

May I kindly ask that you help us with downloading a file from a password-protected website?

 

[JoelBC]

@DarkPremiumCho

As a start, HUGE THANK YOU for assisting in the download of the prime rates. The shell script that you wrote enabled us to automate the download of the prime rates and therefore automatically update our model.

We need to download one more file for our model to completely update overnight and need a second shell script to do so.

Would you be willing to assist in this, we would be most appreciative?

With much thanks,

Joel

 

[DarkPremiumCho]

EDIT: No help needed. We were able to solve the problem.

Will you share how you managed to do that?

May I kindly ask that you help us with downloading a file from a password-protected website?

Maybe. Are you comfortable with me opening the source code?

Would you be willing to assist in this

I'll try my best. I should note my code will also be open source.

 

[LuisMD]

Will you share how you managed to do that?


Maybe. Are you comfortable with me opening the source code?


I'll try my best. I should note my code will also be open source.

1) Of course.

Using Automator, it created a file with ".json?" in the folder we were writing to.
When using the Mac Terminal manually (i.e., bash + the code you provided with modifications to the save folder by myself), it created a file with ".json".
Now when we run the Automator with the modified code, the file already existing in the folder is being overwritten. This (for some reason beyond me) creates the proper file with ".json" at the end.

2) I have no problem with the code being open source. Just for my understanding, the code will be open source without the required username/password, correct? (i.e., we would fill that out on our end).

 

[DarkPremiumCho]

1) Of course.

Using Automator, it created a file with ".json?" in the folder we were writing to.
When using the Mac Terminal manually (i.e., bash + the code you provided with modifications to the save folder by myself), it created a file with ".json".
Now when we run the Automator with the modified code, the file already existing in the folder is being overwritten. This (for some reason beyond me) creates the proper file with ".json" at the end.

2) I have no problem with the code being open source. Just for my understanding, the code will be open source without the required username/password, correct? (i.e., we would fill that out on our end).

Yeah. That's the convention. I must say I will still need a test account to implement the code.

 

[LuisMD]

Yeah. That's the convention. I must say I will still need a test account to implement the code.

Thanks for the willingness to help.

The website we are trying to download from is https://www.chathamfinancial.com/.

The rates we need to download (after logging in) can be found under Market Rates -> Canada ->

Canadian Overnight Repo Rate Average (CORRA)​

What is the best way to provide you the log-in information? I am new to this website and it appears your profile is limited to the public and I cannot send you a private message.

EDIT: if you prefer, please send me your email and I can send you the credentials there; or, we can do this through private messaging.

 

[DarkPremiumCho]

Thanks for the willingness to help.

The website we are trying to download from is https://www.chathamfinancial.com/.

The rates we need to download (after logging in) can be found under Market Rates -> Canada ->

Canadian Overnight Repo Rate Average (CORRA)​

What is the best way to provide you the log-in information? I am new to this website and it appears your profile is limited to the public and I cannot send you a private message.

EDIT: if you prefer, please send me your email and I can send you the credentials there; or, we can do this through private messaging.

Oops. The website doesn’t allow automatic data collection without their written consent.

 

[LuisMD]

Oops. The website doesn’t allow automatic data collection without their written consent.

Thanks for the heads-up. I will write to Chatham Financial and assess whether we can get it.

 

[LuisMD]

Oops. The website doesn’t allow automatic data collection without their written consent.

Update as of October 17th:

We have been in contact with Chatham Financial and we are optimistic that we will get written approval. We expect to hear back from Chatham in the coming days.

One question:

Would it make sense to start the code in preparation of this approval? There is no need to post the code online until we get the written approval.

Let me know about your thoughts on this.

P.S. I understand the intention of keeping the code open source (i.e. posted on this forum), however, is there an alternative means of communication in case you would like sign-in information for the Chatham website?

 

[DarkPremiumCho]

Yeah. Let's start by describing your needs in details. I believe you can send me private messages by going to my forum profile.