Rabu, 26 Oktober 2016

[smf_addin] Digest Number 3826[1 Attachment]

9 Messages

Digest #3826

Messages

Tue Oct 25, 2016 4:24 am (PDT) . Posted by:

Chovek2

Thanks for your help, Randy. It's much appreciated!

Tue Oct 25, 2016 9:54 am (PDT) . Posted by:

rr76012

10/25/2016


Re:


re: Randy - Loop - RchGetRchElementNumbers - RchGetYahooQuotes.xlsm
Hello Randy,


I have a spreadsheet using you addin and used with a loop for years, with no problems what so ever, however now not running smoothly anymore.


A Microsoft Visual Basic pop up message:


Code has been interrupted


Continue - End - Debug - Help


By clicking the continue button, it re-starts the download, and then suddenly stops again and again,
I re-clicking the continue button, and re-starts the download again, I then have to continue hitting the continue button and repeat, repeat ect..


I was wondering if I would review the spreadsheet to see if you know why the macro is now having problems. I have not changed anything in my macro.


Here are 2 place of where the debugging sends the vba to....


Randy, As always thanks for all that you do


rr76012


----------------------------------------------------------
Module:ModGetElementNumber
Sub LoadElementsFromFile(pSuffix As Variant)
On Error GoTo ErrorExit
Open ThisWorkbook.Path & Application.PathSeparator & "smf-elements-" & pSuffix & ".txt" For Input As #1
On Error Resume Next
Do Until EOF(1) = True
Line Input #1, sLine
--> If Left(Trim(sLine), 1) <> "'" Then
iPos1 = InStr(sLine, ";")
aParms(CInt(Left(sLine, iPos1 - 1))) = Mid(sLine, iPos1 + 1)
End If
Loop
Close #1
ErrorExit:
End Sub


----------------------------------------------------------
Public Function RCHGetURLData1(pURL As String, _
Optional ByVal pType As String = "GET") As String

'----------------------------------------------------------> Version 2.0i
' 2008.07.18 -- Expand oHTTP.Status selections for "OK" to include zero
' 2009.01.26 -- Allow "GET" or "POST" requests
' 2014.06.13 -- Add bASync parameter
'----------------------------------------------------------*
On Error GoTo ErrorExit
Dim oHTTP As New XMLHTTP
oHTTP.Open pType, pURL, bASync
'oHTTP.setRequestHeader "User-Agent&quot;, "XMLHTTP/1.0"
oHTTP.Send
--> Do While bASync
DoEvents
If oHTTP.ReadyState = 4 Then Exit Do
Loop
Select Case oHTTP.Status
Case 0: RCHGetURLData1 = oHTTP.responseText
Case 200: RCHGetURLData1 = oHTTP.responseText
Case Else: GoTo ErrorExit
End Select
Exit Function
ErrorExit:
RCHGetURLData1 = vError

End Function


----------------------------------------------------------


Attachment(s) from
1 of 1 File(s)

Tue Oct 25, 2016 10:06 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry. Not a clue here. Everything worked fine for me. Even the two
statements you flagged, I can't imagine why they would cause an
interruption.

You might be better off using the smfUpdateDownloadTable process....

On Tue, Oct 25, 2016 at 9:54 AM, rr76012@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> [Attachment(s) <#m_6803577131515479732_TopText> from rr76012@yahoo.com
> [smf_addin] included below]
>
> 10/25/2016
>
>
> Re:
>
>
> re: Randy - Loop - RchGetRchElementNumbers - RchGetYahooQuotes.xlsm
>
> Hello Randy,
>
>
> I have a spreadsheet using you addin and used with a loop for years, with
> no problems what so ever, however now not running smoothly anymore.
>
>
> A Microsoft Visual Basic pop up message:
>
>
> Code has been interrupted
>
>
> Continue - End - Debug - Help
>
>
> By clicking the continue button, it re-starts the download, and then
> suddenly stops again and again,
>
> I re-clicking the continue button, and re-starts the download again, I
> then have to continue hitting the continue button and repeat, repeat ect..
>
>
> I was wondering if I would review the spreadsheet to see if you know why
> the macro is now having problems. I have not changed anything in my macro.
>
>
> Here are 2 place of where the debugging sends the vba to....
>
>
> Randy, As always thanks for all that you do
>
>
> rr76012
>
>
> ----------------------------------------------------------
> ----------------------------------------------------------
>
> Module:ModGetElementNumber
>
> Sub LoadElementsFromFile(pSuffix As Variant)
>
> On Error GoTo ErrorExit
>
> Open ThisWorkbook.Path & Application.PathSeparator & "smf-elements-" &
> pSuffix & ".txt" For Input As #1
>
> On Error Resume Next
>
> Do Until EOF(1) = True
>
> Line Input #1, sLine
>
> --> If Left(Trim(sLine), 1) <> "'" Then
>
> iPos1 = InStr(sLine, ";")
>
> aParms(CInt(Left(sLine, iPos1 - 1))) = Mid(sLine, iPos1 + 1)
>
> End If
>
> Loop
>
> Close #1
>
> ErrorExit:
>
> End Sub
>
>
> ----------------------------------------------------------
> ----------------------------------------------------------
>
> Public Function RCHGetURLData1(pURL As String, _
>
> Optional ByVal pType As String = "GET") As String
>
>
>
> '----------------------------------------------------------
> ------------------------------------> Version 2.0i
>
> ' 2008.07.18 -- Expand oHTTP.Status selections for "OK" to include zero
>
> ' 2009.01.26 -- Allow "GET" or "POST" requests
>
> ' 2014.06.13 -- Add bASync parameter
>
> '----------------------------------------------------------
> ------------------------------------------------*
>
> On Error GoTo ErrorExit
>
> Dim oHTTP As New XMLHTTP
>
> oHTTP.Open pType, pURL, bASync
>
> 'oHTTP.setRequestHeader "User-Agent&quot;, "XMLHTTP/1.0"
>
> oHTTP.Send
>
> --> Do While bASync
>
> DoEvents
>
> If oHTTP.ReadyState = 4 Then Exit Do
>
> Loop
>
> Select Case oHTTP.Status
>
> Case 0: RCHGetURLData1 = oHTTP.responseText
>
> Case 200: RCHGetURLData1 = oHTTP.responseText
>
> Case Else: GoTo ErrorExit
>
> End Select
>
> Exit Function
>
> ErrorExit:
>
> RCHGetURLData1 = vError
>
> End Function
>
>
> ----------------------------------------------------------
> ----------------------------------------------------------
>
>

Tue Oct 25, 2016 11:09 am (PDT) . Posted by:

rr76012

Hi Randy,

As always thank you.


Have a great day...:+)


and Happy Holloween

Tue Oct 25, 2016 9:54 am (PDT) . Posted by:

philip_r_small


Hi Randy


I have been looking using the table from this page ["http://markets.ft.com/data/world/economic-calendar"] to look at forthcoming events. I hope this makes sense! I have found a couple of issues using the RCHGetTableCell formula.. e.g. =RCHGetTableCell("http://markets.ft.com/research/Economic-Calendar",$b$1,"Datel";,,,,A3,,) where a3 = 1 and b1 =-7, gives you the GMT. However, in trying to produce a full table and altering the a, b and "Date" variables, it appears to cut the first line entry and shows the "4Cast" and "Actual" line as zero.


Is there something basic I am missing?








Tue Oct 25, 2016 10:14 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Try using ">GMT" as your search string instead.

When the add-in finds a table cell with "--" in it, in converts it to a
numeric value of zero.

On Tue, Oct 25, 2016 at 9:54 AM, Philip_R_Small@hotmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I have been looking using the table from this page ["
> http://markets.ft.com/data/world/economic-calendar"] to look at
> forthcoming events. I hope this makes sense! I have found a couple of
> issues using the RCHGetTableCell formula.. e.g. =RCHGetTableCell("http://
> markets.ft.com/research/Economic-Calendar",$b$1,"
> ​​
> Datel",,,,A3,,) where a3 = 1 and b1 =-7, gives you the GMT. However, in
> trying to produce a full table and altering the a, b and "Date" variables,
> it appears to cut the first line entry and shows the "4Cast" and "Actual"
> line as zero.
>
> Is there something basic I am missing?
>
>

Tue Oct 25, 2016 12:55 pm (PDT) . Posted by:

garyhartling

Randy,
I have a VBA routine to read a trades file and place info from that file into a spreadsheet.
I've been copying a formula for the RCHGetYahooHistory function to a cell and then
converting the result to a value, but calculations have to be on for that to work and it's
taking up to a minute to post each trade I'm recording, which is way too long if I have a
couple dozen trades to record.


I'd like to call RCHGetYahooHistory from VBA to get a closing quote for a trade date.
I found some sample code in an old post, but it involves a 25 x 1000 array, sorting and
generating dates. I need something much simpler.


Here's an example of what I have:
A10 = date for quote (given)
B10 = end of day quote (to be retrieved using call to RCHGetYahooHistory)

Z10 = ticker (given)


Thx,
Gary

Tue Oct 25, 2016 1:13 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You just need something like:

Range("B1") = RCHGetYahooHistory(Range("Z1"), Year(Range("A1")),
Month(Range("A1")), Day(Range("A1")), Year(Range("A1")),
Month(Range("A1")), Day(Range("A1")), , "c", 0, pDim1:=1, pDim2:=1)

On Tue, Oct 25, 2016 at 12:55 PM, Gary.Hartling@
​...wrote:

>
> I have a VBA routine to read a trades file and place info from that file
> into a spreadsheet.
>
> I've been copying a formula for the RCHGetYahooHistory function to a cell
> and then
> converting the result to a value, but calculations have to be on for that
> to work and it's
> taking up to a minute to post each trade I'm recording, which is way too
> long if I have a
> couple dozen trades to record.
>
>
> I'd like to call RCHGetYahooHistory from VBA to get a closing quote for a
> trade date.
> I found some sample code in an old post, but it involves a 25 x 1000
> array, sorting and
> generating dates. I need something much simpler.
>
>
> Here's an example of what I have:
>
> A10 = date for quote (given)
>
> B10 = end of day quote (to be retrieved using call to RCHGetYahooHistory)
>
> Z10 = ticker (given)
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 02.00

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)