VBA store data from file in web to matrix

[Axtros]

Hello,

I was trying to get data from the web OMIE. The idea is to get data from all the files between to dates, the files are presented with the first line as a header and a character "*" as end of the current file. Data is separated by ";" and it normally has 26 lines (header, 24 lines of relevant data and the end character), but there are files with 25 or 27 (days where hour is change in this country). Also there are files in .zip, that comprise all the files of the days of a past year (2020,2019,2018...), and I do not know if it can be read as it is.

I do not have a lot of experience in this field of VB, and I try different things, but I have the problem that my code is very slow. When I want to get data for 1 month (30/31 files) it last almost 3 minutes! I will like to get it below 30 seconds if possible. These are the versions, that i manage to make work.

Version 1 (Download .zip and read files online):

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Sub HTML_VBA_Extract_Data_From_Website_To_Excel()
    Dim oXMLHTTP As Object
    Dim sPageHTML  As String
    Dim sURL As String
    Dim fechaInicio, fechaFin As Date
    srcpath = "https://www.omie.es/en/file-download?parents%5B0%5D=marginalpdbc&filename="
    dlpath = ThisWorkbook.Path & "\"
    fechaInicio = Range("B1")
    fechaFin = Range("B2")
    Diff = DateDiff("d", fechaInicio, fechaFin)
    diain = Format(fechaInicio, "dd")
    mesIn = Format(fechaInicio, "mm")
    añoIn = Format(fechaInicio, "yyyy")
    Dim matriz
    ReDim matriz(24 * (Diff + 3), 2)
    Do While añoIn < Format(Now(), "yyyy") And añoIn <= Format(fechaFin, "yyyy")
        Name = "marginalpdbc_" + añoIn + ".zip"
        txtsrc = srcpath + Name
        URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
        fechaInicio = DateAdd("yyyy", 1, fechaInicio)
        diain = "01"
        mesIn = "01"
        añoIn = Format(fechaInicio, "yyyy")
    Loop
    f = 1
    Do While (fechaFin >= fechaInicio)
        Name = "marginalpdbc_" + añoIn + mesIn + diain + ".1"
        txtsrc = srcpath + Name
        Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        oXMLHTTP.Open "GET", txtsrc, False
        oXMLHTTP.send
        sPageHTML = oXMLHTTP.responseText
        aux = Split(sPageHTML, ";")
        i = f
        For f = f To f + 23
            matriz(f - 1, 1) = aux(6 * (f - i) + 5)
            matriz(f - 1, 2) = aux(6 * (f - i) + 6)
            End If
        Next
        f = f + 1
        fechaInicio = DateAdd("d", 1, fechaInicio)
        diain = Format(fechaInicio, "dd")
        mesIn = Format(fechaInicio, "mm")
        añoIn = Format(fechaInicio, "yyyy")
    Loop
End Sub

Version 2 (Download .zip and files, then read files):

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Sub HTML_VBA_Extract_Data_From_Website_To_Excel()
    Dim oXMLHTTP As Object
    Dim sPageHTML  As String
    Dim sURL As String
    Dim fechaInicio, fechaFin As Date
    srcpath = "https://www.omie.es/en/file-download?parents%5B0%5D=marginalpdbc&filename="
    dlpath = ThisWorkbook.Path & "\"
    fechaInicio = Range("B1")
    fechaFin = Range("B2")
    Diff = DateDiff("d", fechaInicio, fechaFin)
    diain = Format(fechaInicio, "dd")
    mesIn = Format(fechaInicio, "mm")
    añoIn = Format(fechaInicio, "yyyy")
    Dim matriz
    ReDim matriz(24 * (Diff + 3), 2)
    Do While añoIn < Format(Now(), "yyyy") And añoIn <= Format(fechaFin, "yyyy")
        Name = "marginalpdbc_" + añoIn + ".zip"
        txtsrc = srcpath + Name
        URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
        fechaInicio = DateAdd("yyyy", 1, fechaInicio)
        diain = "01"
        mesIn = "01"
        añoIn = Format(fechaInicio, "yyyy")
    Loop
    f = 1
    Do While (fechaFin >= fechaInicio)
        Name = "marginalpdbc_" + añoIn + mesIn + diain + ".1"
        txtsrc = srcpath + Name
        Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        oXMLHTTP.Open "GET", txtsrc, False
        oXMLHTTP.send
        sPageHTML = oXMLHTTP.responseText
        aux = Split(sPageHTML, ";")
        i = f
        For f = f To f + 23
            matriz(f - 1, 1) = aux(6 * (f - i) + 5)
            matriz(f - 1, 2) = aux(6 * (f - i) + 6)
            End If
        Next
        f = f + 1
        fechaInicio = DateAdd("d", 1, fechaInicio)
        diain = Format(fechaInicio, "dd")
        mesIn = Format(fechaInicio, "mm")
        añoIn = Format(fechaInicio, "yyyy")
    Loop
End Sub

So, if somebody could help finding a better method or fixing my code I would be very grateful. Thanks for your time

 

[Axtros]

I have copy twice the same code by mistake, this will be the true second version.

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Sub descargar2()
Dim fechaInicio, fechaFin As Date
srcpath = "https://www.omie.es/en/file-download?parents%5B0%5D=marginalpdbc&filename="
dlpath = ThisWorkbook.Path & "\"
fechaInicio = Range("B1")
fechaFin = Range("B2")
Diff = DateDiff("d", fechaInicio, fechaFin)
diain = Format(fechaInicio, "dd")
mesIn = Format(fechaInicio, "mm")
añoIn = Format(fechaInicio, "yyyy")

Dim cadena As String
ReDim matriz(24 * (Diff + 1))
Dim f As Integer

Do While añoIn < Format(Now(), "yyyy") And añoIn <= Format(fechaFin, "yyyy")
    Name = "marginalpdbc_" + añoIn + ".zip"
    txtsrc = srcpath + Name
    URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
    fechaInicio = DateAdd("yyyy", 1, fechaInicio)
    diain = "01"
    mesIn = "01"
    añoIn = Format(fechaInicio, "yyyy")
Loop
f = 0
Do While (fechaFin >= fechaInicio)

        Name = "marginalpdbc_" + añoIn + mesIn + diain + ".1"
        txtsrc = srcpath + Name
        URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
        archivo = dlpath & Name
        Open archivo For Input As #1
        Line Input #1, cadena
        Line Input #1, cadena
        While StrComp(cadena, "*") = 1
            matriz(f) = Split(cadena, ";", Len(cadena) - 1)
            f = f + 1
            Line Input #1, cadena
        Wend
        Close #1
        fechaInicio = DateAdd("d", 1, fechaInicio)
        diain = Format(fechaInicio, "dd")
        mesIn = Format(fechaInicio, "mm")
        añoIn = Format(fechaInicio, "yyyy")
Loop
End Sub

 

[Marc L]

Hi,​

​

your code is nothing as you forgot to mention an example of the content of your variables​

or just attaching at least both source and expected result worksheets​

so as we do not have any experience in guessing, mind reading, …​

 

[Axtros]

Hi,​

​

your code is nothing as you forgot to mention an example of the content of your variables​

or just attaching at least both source and expected result worksheets​

so as we do not have any experience in guessing, mind reading, …​

Thanks for your answer Marc L. It is the fist time I write in this type of forum, and I did not know very much, how to explain the topic, sorry. In my code I request for two dates via excel sheet. then i calculate how many days I need to store, to make the sizes of the matrix. Most of the variable (as diaIn,mesIn,añoIn...) is only for storing the day, month and year to create de filename as you can find in the hyperlink I provide in my first post.
So, example:
date 1: 03/08/2021
date 2: 05/08/2021

matrix size (diff(date 1, date 2)+1)
and the create the filename as said, make the request of each individual file. scrpath is where the individual files are store in that webside. I have explain the source files in the first post. the data on it are 6 colums containing, year, month, day, hour, and 2 Prices.
Example:

MARGINALPDBC;
2021;08;05;1;110.71;110.71;
2021;08;05;2;107.75;107.75;
2021;08;05;3;105.27;105.27;
2021;08;05;4;103.63;103.63;
2021;08;05;5;102.85;102.85;
2021;08;05;6;104;104;
2021;08;05;7;106.37;106.37;
2021;08;05;8;107.12;107.12;
2021;08;05;9;112.66;112.66;
2021;08;05;10;112.1;112.1;
2021;08;05;11;108.14;108.14;
2021;08;05;12;102.85;102.85;
2021;08;05;13;101;101;
2021;08;05;14;97.8;97.8;
2021;08;05;15;83;83;
2021;08;05;16;77.95;77.95;
2021;08;05;17;77.75;77.75;
2021;08;05;18;77.95;77.95;
2021;08;05;19;85;85;
2021;08;05;20;100.5;100.5;
2021;08;05;21;111.91;111.91;
2021;08;05;22;115.27;115.27;
2021;08;05;23;115.02;115.02;
2021;08;05;24;111.91;111.91;
*

And the final output is to store in the matrix in colums: 1) Date 2) Hour 3) Price 1) Price 2 . Example

(data since date 1)
04/08/2021 23 109 109
05/08/2021 1 110.71 110.71
05/08/2021 2 107.75 107.75
05/08/2021 3 105.27 105.27
(data until date 2)
My problem is not in how to read the data from file, It is to make it in a different way to acess to infomation more quickly. Is it well explained now? Do I need to send more information for asking for help? I will be waiting for your response

 

[Marc L]

​

For some reason after the download the source zip file appears to be 'damaged' on my side so I can't help further …​

 

[Axtros]

​

For some reason after the download the source zip file appears to be 'damaged' on my side so I can't help further …​

Oh, so strange. In my case, the file has no problem, and i can unzip it without problem. I tried in other computers, to see if it was a common problem, and it work as well. So thank you for trying, I do not know what could be happening for damaging the file in your case.