MetaTrader 5 Python User Group - how to use Python in Metatrader - page 75

[Alexey Volchanskiy]

MrBrooklin:

Yes, I am opening it in Excel. So far it is not possible to update the quote in the open EUR_USD_QUOTE.csv file in real time.

It was the idea to look at the table and see how the quote changes.

Sincerely, Vladimir.

So, it has nothing to do with Python or Exel. The file was opened and the data was imported into Exel. The program you have to write in it, using the built-in VBA.

You don't need a Python program, it's easier to use VBA. I haven't worked with it much, but I'm sure it can do GET.

[MrBrooklin]

Alexey Volchanskiy:

So it has nothing to do with Python or Exel. You opened the file, the data were read into Exel and that's it. Then you have to write a program in it using embedded VBA

You don't need a Python program, it's easier to use VBA. I haven't worked with it much, but I'm sure it can do GET.

Hello Alexey!

Is it impossible to write a program code in PYTHON in the way that EUR_USD_QUOTE.csv file will be closed and immediately reopened with a new quote?

I don't know anything about VBA yet, but thanks for the tip.

Regards, Vladimir.

[MrBrooklin]

Maxim Dmitrievsky:

You open the file with Excel? So it opens the file monopolistically. You have to allow other applications/users to modify the file somewhere in the settings.

Maybe http://blog.depit.ru/odnovremennaya-rabota-v-e xcel/ will help.

In general, you cannot modify a file opened in another application.

Maxim, how difficult is it to make the file EUR_USD_QUOTE.csv open first, as it was implemented in the source code, and then, after a certain interval, it closes for a second under the influence of the finalized program code and immediately opens again, but with a new quote? And so on to infinity...

Regards, Vladimir.

[Maxim Dmitrievsky]

MrBrooklin:

Maxim, how difficult is it to make EUR_USD_QUOTE.csv file first open as it was implemented in the source code, and after a certain time interval, under the influence of the finalized program code, close for a second and then open again, but with a new quote? And so on to infinity...

Regards, Vladimir.

Try this

import csv
import requests
from bs4 import BeautifulSoup
import os


def get_data(url_to_scrap='https://ru.investing.com/currencies/streaming-forex-rates-majors',
             file=None, save_file="Name.csv"):
    if url_to_scrap is not None:
        header = {'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8',
                  'user-agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:79.0) Gecko/20100101 Firefox/79.0'}
        r = requests.get(url_to_scrap, headers=header)
        data = BeautifulSoup(r.content, 'html.parser')
    else:
        data = BeautifulSoup(open(file), 'html.parser')
    table = data.find('tr', id='pair_1')
    table = table.find_all(class_='pid-1-bid')
    row_data = []
    for row in table:
        row_data.append([row.get_text()])
    with open(save_file, 'w') as save:
        for row in row_data:
            writer = csv.writer(save, delimiter=';')
            writer.writerow(row)
    os.startfile(save_file)


from datetime import datetime
import time


t = datetime.now().hour
t2 = t

while True:
    t = datetime.now().hour
    if(t2 != t):
        os.system("TASKKILL /F /IM excel.exe")
        get_data(save_file='EUR_USD_QUOTE.csv')
        t2 = t
    time.sleep(10)

[nicholish en]

Alexey Volchanskiy:

So neither Python nor Exel had anything to do with it. You opened the file, the data in Exel was counted and that's it. It is then necessary to write a program in it on the built-in VBA

And if so, the program on Python is not needed, it is easier to do everything at once in the VBA. I didn't work with him much, but I'm sure he can do GET.

You don't even need VBA. All you have to do is use excel initially. I am going to write instructions in English... Create a new workbook. Click the Data Tab > Get Data > From Other Sources > From Web, then paste the web address in the url field. Next click refresh all more options > Connection properties and configure to update automatically every n minutes.

[nicholish en]

MrBrooklin:

Maxim, how difficult is it to make the EUR_USD_QUOTE.csv file open first, as it was implemented in the source code, and at some interval of time under the influence of modified code for a second closed and immediately reopened, but with a new quote? And so to infinity...

Respectfully, Vladimir.

The file will be locked for writing by excel if you leave the excel window open. You will need to add logic to account for a locked file. Also, why are you scraping the web for quotes? Why aren't you using MetaTrader to gather quotes? It doesn't make sense.

import itertools
import os
import pathlib

import bs4
import requests


def main():
    url = 'https://ru.investing.com/currencies/streaming-forex-rates-majors'
    r = requests.get(url, headers={'user-agent': 'Python Agent'})
    soup = bs4.BeautifulSoup(r.content, 'lxml')
    eurusd_bid = soup.find('td', 'pid-1-bid').text.replace(',', '.')
    for i in itertools.count():
        file = pathlib.Path() / f'eurusd_bid_{i}.csv'
        try:
            file.write_text(eurusd_bid)
            os.startfile(file)
            break
        except PermissionError:
            continue


if __name__ == '__main__':
    main()

[MrBrooklin]

Maxim Dmitrievsky:

try this.

Thank you, Maxim!

I'm already trying it. The loop has started, but the file is not opening yet. I will leave the loop in operation and wait.

Regards, Vladimir.

P.S. Pardon! Didn't pay attention at once, that it was worth an hour. I changed it to minute EUR_USD_QUOTE.csv file was opened but I got this message in Run tab of Pycharm:

�訡��: �� 㤠���� ���� ����� "excel.exe".

Then after another minute or so, the file closed and reopened, but in Exel a tab appeared on the left side of the spreadsheet asking me to save the previously opened text. I waited a bit longer to see what would happen next, but then the cycle stopped and a message like this appeared:

�ᯥ譮: ����� "EXCEL.EXE", � �����䨪��஬ 4208, � �����襭.
�ᯥ譮: ����� "EXCEL.EXE", � �����䨪��஬ 3168, �� �����襭.
Traceback (most recent call last):
File "C:/Users/Vladimir/PycharmProjects/My_Python_Project/9.py", line 37, in <module>
get_data(save_file='EUR_USD_QUOTE.csv')
File "C:/Users/Vladimir/PycharmProjects/My_Python_Project/9.py", line 23, in get_data
with open(save_file, 'w') as save:
PermissionError: [Errno 13] Permission denied: 'EUR_USD_QUOTE.csv'

This is the result so far, but I already made progress!

Regards, Vladimir.

[MrBrooklin]

nicholish en:

The file will be locked for writing by excel if you leave the excel window open. You will need to add logic to account for a locked file. Also, why are you scraping the web for quotes? Why aren't you using MetaTrader to gather quotes? It doesn't make sense.

The file will be locked for writing to Excel, if you leave the Excel window open. You will need to add logic to account for the locked file. Also, why are you looking for quotes on the internet? Why don't you use MetaTrader to collect quotes? That doesn't make sense. (From me personally: Sorry, nicholish en, I don't speak English, so I used Google Translate)

I want to learn how to take quotes from the Internet using Python language, so I can look them not in terminal, but in some file, for example csv . I thought it was a very basic task, but it turns out it's not. Maybe I'm on the wrong track, but he who looks for nothing, finds nothing.

Now let's move on to the code you suggested. After I have launched the code in Pycharm I saw the following message:

Traceback (most recent call last):
File "C:/Users/Vladimir/PycharmProjects/My_Python_Project/10.py", line 25, in <module>
main()
File "C:/Users/Vladimir/PycharmProjects/My_Python_Project/10.py", line 13, in main
eurusd_bid = soup.find('td', 'pid-1-bid').text.replace(',', '.')
AttributeError: 'NoneType' object has no attribute 'text'

Process finished with exit code 1

What should I change to see how the code works?

My respect, Vladimir.

[MrBrooklin]

Forum on trading, automated trading systems and trading strategy testing

MetaTrader 5 Python User Group - How to use Python in Metatrader

MrBrooklin, 2020.08.19 22:05

Thank you, Maxim!

Already trying it out. The loop is running, but the file is not opening yet. I will leave the loop in operation and wait.

Regards, Vladimir.

P.S. Pardon! Didn't pay attention at once, that it was worth an hour. I changed it to minute EUR_USD_QUOTE.csv file was opened but I got this message in Run tab of Pycharm:

�訡��: �� 㤠���� ���� ����� "excel.exe".

Then after another minute or so, the file closed and reopened, but in Exel a tab appeared on the left side of the spreadsheet asking me to save the previously opened text. I waited a bit longer to see what would happen next, but then the cycle stopped and a message like this appeared:

�ᯥ譮: ����� "EXCEL.EXE", � �����䨪��஬ 4208, � �����襭.
�ᯥ譮: ����� "EXCEL.EXE", � �����䨪��஬ 3168, �� �����襭.
Traceback (most recent call last):
File "C:/Users/Vladimir/PycharmProjects/My_Python_Project/9.py", line 37, in <module>
get_data(save_file='EUR_USD_QUOTE.csv')
File "C:/Users/Vladimir/PycharmProjects/My_Python_Project/9.py", line 23, in get_data
with open(save_file, 'w') as save:
PermissionError: [Errno 13] Permission denied: 'EUR_USD_QUOTE.csv'

This is the result so far, but I already made progress!

Sincerely, Vladimir.

Maxim, I don't know what happened but I tried your code again and oh my goodness! The looping process has started, but with one caveat. As before, a tab appeared in Excel on the left side asking me to save the file, but it's not a big deal. Attached is a screenshot. I waited for the six cycles to pass and quit the program. Now I will study each line of code and try to understand what I've written and what it is intended for. Thank you very much!

Sincerely, Vladimir.

P.S. An idea came up, why a tab appears on the left hand side suggesting to save the file. Just the programming code closes the file without saving, that's probably why Exel offers and will continue to offer to save the file.

[Алексей Тарабанов]

Reading the Chinese pager. Doing a lot of thinking...

That's my point about integrating Python and MQL. Both languages are close, but MQL is simple and Python is primitive, like BASIC. Everyone who programs in MQL can easily build something in Python and vice versa. What is the interest in this subject? Firstly, it is for very lazy people. Well, write something in a very similar language... The interest could be in two components: libraries, of which there are many in Python, and interfaces, for which MQL is famous. So why not make access from Python to MQL interfaces and access from MQL to Python libraries? That's it.