6 Messages
Digest #3074
Messages
Sat May 24, 2014 1:00 pm (PDT) . Posted by:
"andrei radulescu-banu" iubica2
In trying to understand why Excel was taking too long to load my spread
sheet, I was able to make a small change to RCHGetURLData(), logging the
web site URLs that were being accessed, and the download times. The new
code is highlighted in yellow.
There are many other ways to do this - and this is not the only way. I'm
just emailing this in hope this could be helpful to others... My
spreadsheet has nearly 70 symbols, and turns out one of the multiple pages
downloaded per symbol was particularly slow. This little exercise allowed
me to get the same data from another, faster page. As a result, I was able
to bring my load time down from 5-6 minutes to 2.
Andrei
Public Function RCHGetURLData(ByVal pURL As String, _
Optional ByVal pUseIE As Integer = 0) As String
'---------------------------------------------------------->
Version 2.0i
' 2009.01.26 -- Add pUseIE options of 2 and 3
' 2009.03.16 -- Add documentation
'----------------------------------------------------------*
Dim fileName As String
Dim s As String
Dim n As Integer
Dim sngStart As Single, sngEnd As Single, sngElapsed As Single, sngTOD
As Single
'fileName = "C:\doc\tmp\log.txt" ' Comment in to turn file logging on;
folder must exist beforehand
If fileName <> "" Then
n = FreeFile()
Open "C:\doc\tmp\log.txt" For Append As #n
sngStart = Timer ' Get start time.
End If
Select Case True
Case pUseIE = 1: RCHGetURLData = RCHGetURLData2(pURL) ' IE
Object
Case pUseIE = 2: RCHGetURLData = RCHGetURLData3(pURL) '
HTMLDocument
Case pUseIE = 3: RCHGetURLData = RCHGetURLData1(pURL, "POST") '
XMLHTTP Post
Case Else: RCHGetURLData = RCHGetURLData1(pURL) '
XMLHTTP Get
End Select
If fileName <> "" Then
sngEnd = Timer ' Get start time.
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.
sngTOD = Format(sngStart, "Fixed") ' The time of day
s = sngTOD & ": " & "URL took " & sngElapsed & ": " & pURL
Print #n, s ' write to file
Close #n
End If
End Function
--
==================================
Andrei Radulescu-Banu
86 Cedar St, Lexington MA
617.216.8509 (m), 781.862.5854 (h)
lex-wiki.org, lex4lang.org,
andrei4schools.com, bitdribble.com
==================================
sheet, I was able to make a small change to RCHGetURLData(
web site URLs that were being accessed, and the download times. The new
code is highlighted in yellow.
There are many other ways to do this - and this is not the only way. I'm
just emailing this in hope this could be helpful to others... My
spreadsheet has nearly 70 symbols, and turns out one of the multiple pages
downloaded per symbol was particularly slow. This little exercise allowed
me to get the same data from another, faster page. As a result, I was able
to bring my load time down from 5-6 minutes to 2.
Andrei
Public Function RCHGetURLData(
Optional ByVal pUseIE As Integer = 0) As String
'-------
Version 2.0i
' 2009.01.26 -- Add pUseIE options of 2 and 3
' 2009.03.16 -- Add documentation
'-------
Dim fileName As String
Dim s As String
Dim n As Integer
Dim sngStart As Single, sngEnd As Single, sngElapsed As Single, sngTOD
As Single
'fileName = "C:\
folder must exist beforehand
If fileName <> "" Then
n = FreeFile()
Open "C:\
sngStart = Timer ' Get start time.
End If
Select Case True
Case pUseIE = 1: RCHGetURLData = RCHGetURLData2(
Object
Case pUseIE = 2: RCHGetURLData = RCHGetURLData3(
HTMLDocument
Case pUseIE = 3: RCHGetURLData = RCHGetURLData1(
XMLHTTP Post
Case Else: RCHGetURLData = RCHGetURLData1(
XMLHTTP Get
End Select
If fileName <> "" Then
sngEnd = Timer ' Get start time.
sngElapsed = Format(sngEnd - sngStart, "Fixed"
sngTOD = Format(sngStart, "Fixed"
s = sngTOD & ": " & "URL took " & sngElapsed & ": " & pURL
Print #n, s ' write to file
Close #n
End If
End Function
--
============
Andrei Radulescu-Banu
86 Cedar St, Lexington MA
617.216.8509 (m), 781.862.5854 (h)
lex-wiki.org, lex4lang.org,
andrei4schools.
============
Sat May 24, 2014 5:07 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Interesting idea. I can think of a few times it would have been very handy
to have. I may do it as a CSV file though. Easier to open and use right
away.
Although the file might get very large if using a lot of MSN elements. That
URL is very long. I wonder if the URL should be truncated, or maybe just
trim down to the domain only? Hmmm.
Does time become an issue as data is appended to a larger and larger file?
I wonder if the file would need to be managed? Maybe use an array instead,
that can be dumped into the workbook? Then it just exists for the session.
Hmmm.
On Sat, May 24, 2014 at 1:00 PM, andrei radulescu-banu
bitdribble@gmail.comwrote:
>
> In trying to understand why Excel was taking too long to load my spread
> sheet, I was able to make a small change to RCHGetURLData(), logging the
> web site URLs that were being accessed, and the download times. The new
> code is highlighted in yellow.
>
> There are many other ways to do this - and this is not the only way. I'm
> just emailing this in hope this could be helpful to others... My
> spreadsheet has nearly 70 symbols, and turns out one of the multiple pages
> downloaded per symbol was particularly slow. This little exercise allowed
> me to get the same data from another, faster page. As a result, I was able
> to bring my load time down from 5-6 minutes to 2.
>
to have. I may do it as a CSV file though. Easier to open and use right
away.
Although the file might get very large if using a lot of MSN elements. That
URL is very long. I wonder if the URL should be truncated, or maybe just
trim down to the domain only? Hmmm.
Does time become an issue as data is appended to a larger and larger file?
I wonder if the file would need to be managed? Maybe use an array instead,
that can be dumped into the workbook? Then it just exists for the session.
Hmmm.
On Sat, May 24, 2014 at 1:00 PM, andrei radulescu-banu
bitdribble@gmail.comwrote:
>
> In trying to understand why Excel was taking too long to load my spread
> sheet, I was able to make a small change to RCHGetURLData(
> web site URLs that were being accessed, and the download times. The new
> code is highlighted in yellow.
>
> There are many other ways to do this - and this is not the only way. I'm
> just emailing this in hope this could be helpful to others... My
> spreadsheet has nearly 70 symbols, and turns out one of the multiple pages
> downloaded per symbol was particularly slow. This little exercise allowed
> me to get the same data from another, faster page. As a result, I was able
> to bring my load time down from 5-6 minutes to 2.
>
Sat May 24, 2014 7:56 pm (PDT) . Posted by:
"andrei radulescu-banu" iubica2
>
> Interesting idea. I can think of a few times it would have been very
> handy to have. I may do it as a CSV file though. Easier to open and use
> right away.
>
Easily done by using comma separators instead of space.
> Although the file might get very large if using a lot of MSN elements.
> That URL is very long. I wonder if the URL should be truncated, or maybe
> just trim down to the domain only? Hmmm.
>
If written as CSV, and loaded in Excel, then Excel itself can truncate the
URL or post-process it in some other way.
> Does time become an issue as data is appended to a larger and larger file?
>
No, the accumulated file does not cause overhead, because the file is open
in 'append' mode. In my case, I had about 70 symbols, about 6 URLs accessed
per symbol, and there was no noticeable overhead. But it would be slicker
to find a way not to re-open the file each time a log message is saved,
then closing it immediately afterwards... I am sure there is a VBA way to
do this. If it were C/C++, the file handle could be a static variable,
opened once. I have not learned yet how to execute global initializers in
VBA... that's in essence what would be needed. This
page<http://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value>seems
to indicate a way to do that in VBA.
> I wonder if the file would need to be managed? Maybe use an array instead,
> that can be dumped into the workbook? Then it just exists for the session.
>
It could very well be instead a large array dumped into a separate
workbook, created on the fly if needed, perhaps cleared when
smfForcedRecalculation is called.
Regards,
Andrei
> Interesting idea. I can think of a few times it would have been very
> handy to have. I may do it as a CSV file though. Easier to open and use
> right away.
>
Easily done by using comma separators instead of space.
> Although the file might get very large if using a lot of MSN elements.
> That URL is very long. I wonder if the URL should be truncated, or maybe
> just trim down to the domain only? Hmmm.
>
If written as CSV, and loaded in Excel, then Excel itself can truncate the
URL or post-process it in some other way.
> Does time become an issue as data is appended to a larger and larger file?
>
No, the accumulated file does not cause overhead, because the file is open
in 'append' mode. In my case, I had about 70 symbols, about 6 URLs accessed
per symbol, and there was no noticeable overhead. But it would be slicker
to find a way not to re-open the file each time a log message is saved,
then closing it immediately afterwards... I am sure there is a VBA way to
do this. If it were C/C++, the file handle could be a static variable,
opened once. I have not learned yet how to execute global initializers in
VBA... that's in essence what would be needed. This
page<http://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value>seems
to indicate a way to do that in VBA.
> I wonder if the file would need to be managed? Maybe use an array instead,
> that can be dumped into the workbook? Then it just exists for the session.
>
It could very well be instead a large array dumped into a separate
workbook, created on the fly if needed, perhaps cleared when
smfForcedRecalculat
Regards,
Andrei
Sat May 24, 2014 8:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm playing around with it right now.
I went with a function to turn logging on and off. For example:
=smfLogInternetCalls("Y")
=smfLogInternetCalls("N")
I can also delete the file (also sets logging mode off), or reset the file
(also sets logging mode on), with:
=smfLogInternetCalls("Delete")
=smfLogInternetCalls("Reset")
I'm creating a CSV file with 3 fields -- date/time stamp, elapsed time, URL
(first 100 bytes).
I also created a new macro, smfOpenLogFile, that opens the CSV file, adds a
locked header line, and formats the date and elapsed time columns.
On Sat, May 24, 2014 at 7:56 PM, andrei radulescu-banu bitdribble@... wrote:
> Interesting idea. I can think of a few times it would have been very
>> handy to have. I may do it as a CSV file though. Easier to open and use
>> right away.
>>
>
> Easily done by using comma separators instead of space.
>
>
>> Although the file might get very large if using a lot of MSN elements.
>> That URL is very long. I wonder if the URL should be truncated, or maybe
>> just trim down to the domain only? Hmmm.
>>
>
> If written as CSV, and loaded in Excel, then Excel itself can truncate the
> URL or post-process it in some other way.
>
>
>> Does time become an issue as data is appended to a larger and larger
>> file?
>>
>
> No, the accumulated file does not cause overhead, because the file is open
> in 'append' mode. In my case, I had about 70 symbols, about 6 URLs accessed
> per symbol, and there was no noticeable overhead. But it would be slicker
> to find a way not to re-open the file each time a log message is saved,
> then closing it immediately afterwards... I am sure there is a VBA way to
> do this. If it were C/C++, the file handle could be a static variable,
> opened once. I have not learned yet how to execute global initializers in
> VBA... that's in essence what would be needed. This page<http://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value>seems to indicate a way to do that in VBA.
>
>
>> I wonder if the file would need to be managed? Maybe use an array
>> instead, that can be dumped into the workbook? Then it just exists for the
>> session.
>>
>
> It could very well be instead a large array dumped into a separate
> workbook, created on the fly if needed, perhaps cleared when
> smfForcedRecalculation is called.
>
>
I went with a function to turn logging on and off. For example:
=smfLogInternetCalls("Y")
=smfLogInternetCalls("N")
I can also delete the file (also sets logging mode off), or reset the file
(also sets logging mode on), with:
=smfLogInternetCalls("Delete")
=smfLogInternetCalls("Reset")
I'm creating a CSV file with 3 fields -- date/time stamp, elapsed time, URL
(first 100 bytes).
I also created a new macro, smfOpenLogFile, that opens the CSV file, adds a
locked header line, and formats the date and elapsed time columns.
On Sat, May 24, 2014 at 7:56 PM, andrei radulescu-banu bitdribble@... wrote:
> Interesting idea. I can think of a few times it would have been very
>> handy to have. I may do it as a CSV file though. Easier to open and use
>> right away.
>>
>
> Easily done by using comma separators instead of space.
>
>
>> Although the file might get very large if using a lot of MSN elements.
>> That URL is very long. I wonder if the URL should be truncated, or maybe
>> just trim down to the domain only? Hmmm.
>>
>
> If written as CSV, and loaded in Excel, then Excel itself can truncate the
> URL or post-process it in some other way.
>
>
>> Does time become an issue as data is appended to a larger and larger
>> file?
>>
>
> No, the accumulated file does not cause overhead, because the file is open
> in 'append' mode. In my case, I had about 70 symbols, about 6 URLs accessed
> per symbol, and there was no noticeable overhead. But it would be slicker
> to find a way not to re-open the file each time a log message is saved,
> then closing it immediately afterwards... I am sure there is a VBA way to
> do this. If it were C/C++, the file handle could be a static variable,
> opened once. I have not learned yet how to execute global initializers in
> VBA... that's in essence what would be needed. This page<http://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value>seems to indicate a way to do that in VBA.
>
>
>> I wonder if the file would need to be managed? Maybe use an array
>> instead, that can be dumped into the workbook? Then it just exists for the
>> session.
>>
>
> It could very well be instead a large array dumped into a separate
> workbook, created on the fly if needed, perhaps cleared when
> smfForcedRecalculat
>
>
Sat May 24, 2014 8:41 pm (PDT) . Posted by:
"andrei radulescu-banu" iubica2
Perfect... If you need beta testing, please send the code over.
Andrei
On Sat, May 24, 2014 at 11:35 PM, Randy Harmelink
rharmelink@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:
>
>
> I'm playing around with it right now.
>
> I went with a function to turn logging on and off. For example:
>
> =smfLogInternetCalls("Y")
> =smfLogInternetCalls("N")
>
> I can also delete the file (also sets logging mode off), or reset the file
> (also sets logging mode on), with:
>
> =smfLogInternetCalls("Delete")
> =smfLogInternetCalls("Reset")
>
> I'm creating a CSV file with 3 fields -- date/time stamp, elapsed time,
> URL (first 100 bytes).
>
> I also created a new macro, smfOpenLogFile, that opens the CSV file, adds
> a locked header line, and formats the date and elapsed time columns.
>
> On Sat, May 24, 2014 at 7:56 PM, andrei radulescu-banu bitdribble@...
> wrote:
>
>> Interesting idea. I can think of a few times it would have been very
>>> handy to have. I may do it as a CSV file though. Easier to open and use
>>> right away.
>>>
>>
>> Easily done by using comma separators instead of space.
>>
>>
>>> Although the file might get very large if using a lot of MSN elements.
>>> That URL is very long. I wonder if the URL should be truncated, or maybe
>>> just trim down to the domain only? Hmmm.
>>>
>>
>> If written as CSV, and loaded in Excel, then Excel itself can truncate
>> the URL or post-process it in some other way.
>>
>>
>>> Does time become an issue as data is appended to a larger and larger
>>> file?
>>>
>>
>> No, the accumulated file does not cause overhead, because the file is
>> open in 'append' mode. In my case, I had about 70 symbols, about 6 URLs
>> accessed per symbol, and there was no noticeable overhead. But it would be
>> slicker to find a way not to re-open the file each time a log message is
>> saved, then closing it immediately afterwards... I am sure there is a VBA
>> way to do this. If it were C/C++, the file handle could be a static
>> variable, opened once. I have not learned yet how to execute global
>> initializers in VBA... that's in essence what would be needed. This page<http://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value>seems to indicate a way to do that in VBA.
>>
>>
>>> I wonder if the file would need to be managed? Maybe use an array
>>> instead, that can be dumped into the workbook? Then it just exists for the
>>> session.
>>>
>>
>> It could very well be instead a large array dumped into a separate
>> workbook, created on the fly if needed, perhaps cleared when
>> smfForcedRecalculation is called.
>>
>>
>
>
--
==================================
Andrei Radulescu-Banu
86 Cedar St, Lexington MA
617.216.8509 (m), 781.862.5854 (h)
lex-wiki.org, lex4lang.org,
andrei4schools.com, bitdribble.com
==================================
Andrei
On Sat, May 24, 2014 at 11:35 PM, Randy Harmelink
rharmelink@gmail.com[smf_addin]
<smf_addin@yahoogroups.com> wrote:
>
>
> I'm playing around with it right now.
>
> I went with a function to turn logging on and off. For example:
>
> =smfLogInternetCalls("Y")
> =smfLogInternetCalls("N")
>
> I can also delete the file (also sets logging mode off), or reset the file
> (also sets logging mode on), with:
>
> =smfLogInternetCalls("Delete")
> =smfLogInternetCalls("Reset")
>
> I'm creating a CSV file with 3 fields -- date/time stamp, elapsed time,
> URL (first 100 bytes).
>
> I also created a new macro, smfOpenLogFile, that opens the CSV file, adds
> a locked header line, and formats the date and elapsed time columns.
>
> On Sat, May 24, 2014 at 7:56 PM, andrei radulescu-banu bitdribble@...
> wrote:
>
>> Interesting idea. I can think of a few times it would have been very
>>> handy to have. I may do it as a CSV file though. Easier to open and use
>>> right away.
>>>
>>
>> Easily done by using comma separators instead of space.
>>
>>
>>> Although the file might get very large if using a lot of MSN elements.
>>> That URL is very long. I wonder if the URL should be truncated, or maybe
>>> just trim down to the domain only? Hmmm.
>>>
>>
>> If written as CSV, and loaded in Excel, then Excel itself can truncate
>> the URL or post-process it in some other way.
>>
>>
>>> Does time become an issue as data is appended to a larger and larger
>>> file?
>>>
>>
>> No, the accumulated file does not cause overhead, because the file is
>> open in 'append' mode. In my case, I had about 70 symbols, about 6 URLs
>> accessed per symbol, and there was no noticeable overhead. But it would be
>> slicker to find a way not to re-open the file each time a log message is
>> saved, then closing it immediately afterwards... I am sure there is a VBA
>> way to do this. If it were C/C++, the file handle could be a static
>> variable, opened once. I have not learned yet how to execute global
>> initializers in VBA... that's in essence what would be needed. This page<http://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value>seems to indicate a way to do that in VBA.
>>
>>
>>> I wonder if the file would need to be managed? Maybe use an array
>>> instead, that can be dumped into the workbook? Then it just exists for the
>>> session.
>>>
>>
>> It could very well be instead a large array dumped into a separate
>> workbook, created on the fly if needed, perhaps cleared when
>> smfForcedRecalculat
>>
>>
>
>
--
============
Andrei Radulescu-Banu
86 Cedar St, Lexington MA
617.216.8509 (m), 781.862.5854 (h)
lex-wiki.org, lex4lang.org,
andrei4schools.
============
Sat May 24, 2014 9:26 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I uploaded the current status of the add-in to the "Works in Progress"
folder on the web site:
RCH_Stock_Market_Functions-2.1.2014.05.24.zip
The current change log is there as well, so you can see the changes made
since January. If you use the smfUpdateDownloadTable process, one change I
made is that you can highlight a portion of the table -- range, columns, or
rows -- and it should only update those highlighted items.
On Sat, May 24, 2014 at 8:41 PM, andrei radulescu-banu bitdribble@... wrote:
>
> Perfect... If you need beta testing, please send the code over.
>
> On Sat, May 24, 2014 at 11:35 PM, Randy Harmelink rharmelink@...<rharmelink@gmail.com>wrote:
>
>>
>> I'm playing around with it right now.
>>
>> I went with a function to turn logging on and off. For example:
>>
>> =smfLogInternetCalls("Y")
>> =smfLogInternetCalls("N")
>>
>> I can also delete the file (also sets logging mode off), or reset the
>> file (also sets logging mode on), with:
>>
>> =smfLogInternetCalls("Delete")
>> =smfLogInternetCalls("Reset")
>>
>> I'm creating a CSV file with 3 fields -- date/time stamp, elapsed time,
>> URL (first 100 bytes).
>>
>> I also created a new macro, smfOpenLogFile, that opens the CSV file, adds
>> a locked header line, and formats the date and elapsed time columns.
>>
>>
folder on the web site:
RCH_Stock_Market_Functions-2.1.2014.05.24.zip
The current change log is there as well, so you can see the changes made
since January. If you use the smfUpdateDownloadTable process, one change I
made is that you can highlight a portion of the table -- range, columns, or
rows -- and it should only update those highlighted items.
On Sat, May 24, 2014 at 8:41 PM, andrei radulescu-banu bitdribble@... wrote:
>
> Perfect... If you need beta testing, please send the code over.
>
> On Sat, May 24, 2014 at 11:35 PM, Randy Harmelink rharmelink@...<rharmelink@gmail.com>wrote:
>
>>
>> I'm playing around with it right now.
>>
>> I went with a function to turn logging on and off. For example:
>>
>> =smfLogInternetCall
>> =smfLogInternetCall
>>
>> I can also delete the file (also sets logging mode off), or reset the
>> file (also sets logging mode on), with:
>>
>> =smfLogInternetCall
>> =smfLogInternetCall
>>
>> I'm creating a CSV file with 3 fields -- date/time stamp, elapsed time,
>> URL (first 100 bytes).
>>
>> I also created a new macro, smfOpenLogFile, that opens the CSV file, adds
>> a locked header line, and formats the date and elapsed time columns.
>>
>>
Tidak ada komentar:
Posting Komentar