Kamis, 29 Mei 2014

[smf_addin] Digest Number 3078

7 Messages

Digest #3078

Messages

Wed May 28, 2014 3:52 pm (PDT) . Posted by:

pragaon

Hi,


I am visiting the group after a long time, so it is like I have totally forgotten how to use SMF again!
But it will come back to me in no time with some pointers...
I have created a portfiolio in google finance. The following link will creare a csv file (for the real time stock prices) and download it in download folder using the browser as portfolioname.csv


https://www.google.com/finance/portfolio?pid=1&output=csv https://www.google.com/finance/portfolio?pid=2&output=csv



note that the pid=1 is my first portfolio and it is aclled by the index number and not by portfolio's name.
the


I want to call this link from excel itself and same the csv data in the same excel file.
any pointers?


thanks,
Prakash


Wed May 28, 2014 4:28 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

This worked fine for my Google Portfolio:

=smfGetCSVFile("https://www.google.com/finance/portfolio?pid=1&output=csv")

On Wed, May 28, 2014 at 3:52 PM, pragaon@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I have created a portfiolio in google finance. The following link will
> creare a csv file (for the real time stock prices) and download it in
> download folder using the browser as portfolioname.csv
>
> https://www.google.com/finance/portfolio?pid=1&output=csv<https://www.google.com/finance/portfolio?pid=2&output=csv>
>
> note that the pid=1 is my first portfolio and it is aclled by the index
> number and not by portfolio's name.
>
> I want to call this link from excel itself and same the csv data in the
> same excel file.
>
> any pointers?
>
>

Wed May 28, 2014 4:46 pm (PDT) . Posted by:

pragaon

Thanks Randy. the output csv file is from A1 to J50 i.e. 50 rows and a-J columns.
The above formula in cell a1 extracts the a1 content only.


do I have to select the grid and group it and then use above formula?


Wed May 28, 2014 4:50 pm (PDT) . Posted by:

pragaon

Got It..."control -shift-enter" I knew it will all come back to me...:-)


Wed May 28, 2014 8:58 pm (PDT) . Posted by:

"andrei radulescu-banu" iubica2

Here is another idea for a small modification. My spreadsheet has now ~70
symbols, and sometimes the referring web page for just one ticker among all
is temporarily failing. This causes all computations depending on that cell
to give error.

One can always execute the smfForceRecalculate() macro (saved in a top
toolbar button), but this causes all other 69 symbols to reload, and now
another symbol may fail to load one of the referring web pages.

As a workaround, here is a small modification to modUtilities: two new
macros, one disabling the page cache, one enabling it. The cache is by
default enabled. When a cell does not load, I can disable the cache, then
click and type 'enter' just on that cell, then reenable the cache.

One pitfall: if several cells depend on the same flaky URL, one has to
click and type 'enter' on all of them. But, depending on the sheet layout,
it's usually pretty obvious that the cell needs to be reloaded.

Another alternative, not coded here, is to implement a retry mechanism in
the RCHGetURLData() routine for the case when we get errors (assuming
there's a way to tell programmatically that the page is not fully loaded -
which requires further investigation, and may not be possible).

Here is the code, with changes highlighted in yellow. This code is provided
as illustration only - as usual, there are several ways to code this, and
there are for sure better ways.

Cheers,
Andrei

Public Const kPages = 1000 ' Number of data pages to
save
Public vError As Variant ' Value to return if error
Public aData(1 To kPages, 1 To 2) As String ' Saved web page data (2)
and its ticker-source (1)
Public sData(1 To 3) As String ' 1 = Raw data, 2 =
Stripped data, 3 = Upper case of stripped data
Public sLog As String
Public sWebCache As String ' "" or "Y" for enabled,
"N" for disabled

Public Function smfLogInternetCalls(pLog As String)

'----------------------------------------------------------*
' 2014.05.24 -- Created. Function to control whether URL calls are
logged into a CSV file

'----------------------------------------------------------*
On Error Resume Next
sLog = UCase(pLog)
Select Case sLog
Case "Y": smfLogInternetCalls = "Logging on"
Case "DELETE"
Kill ThisWorkbook.Path & "\smf-log.csv"
sLog = "N"
smfLogInternetCalls = "Logging off, file deleted"
Case "RESET"
Kill ThisWorkbook.Path & "\smf-log.csv"
sLog = "Y"
smfLogInternetCalls = "Logging on, file reset"
Case Else: smfLogInternetCalls = "Logging off"
End Select
End Function
Public Sub smfOpenLogFile()

'----------------------------------------------------------*
' 2014.05.24 -- Created. Macro to open and format SMF log file

'----------------------------------------------------------*
Workbooks.Open Filename:=ThisWorkbook.Path & "\smf-log.csv"
Range("A1").EntireRow.Insert
Range("A1").Value = "Time Stamp"
Range("B1").Value = "Duration"
Range("C1").Value = "Called URL"
Columns("A:A").NumberFormat = "yyyy-mm-dd hh:mm:ss"
Columns("A:A").HorizontalAlignment = xlCenter
Columns("B:B").NumberFormat = "0.0000"
Columns("B:B").HorizontalAlignment = xlRight
Columns("C:C").ColumnWidth = 99.86
Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub

Public Sub smfEnableWebCache()
sWebCache = "Y"
End Sub

Public Sub smfDisableWebCache()
sWebCache = "N"
End Sub

Public Sub smfForceRecalculation()
iMorningStar = 0
sAdvFNPrefix = ""
iInit = 0
For i1 = 1 To kPages
aData(i1, 1) = "" ' Reset stored ticker array
Next i1
If Val(Application.Version) < 10 Then
Application.CalculateFull
Else
Application.CalculateFullRebuild
End If
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

'----------------------------------------------------------*
On Error GoTo ErrorExit
Dim oHTTP As New XMLHTTP
oHTTP.Open pType, pURL, False
oHTTP.Send
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

Public Function RCHGetURLData2(pURL As String) As String
On Error GoTo ErrorExit
Dim oIE As Object
Set oIE = CreateObject("InternetExplorer.Application")
oIE.Visible = False
With oIE
.Navigate pURL
Do Until Not .Busy
DoEvents
Loop
RCHGetURLData2 = .Document.documentElement.outerHTML
.Quit
End With
Set oIE = Nothing
Exit Function
ErrorExit:
RCHGetURLData2 = vError
End Function

Public Function RCHGetURLData3(pURL As String) As String

'---------------------------------------------------------->
Version 2.0i
' 2009.01.26 -- Drop ".Document" qualifier

'---------------------------------------------------------->
Version 2.0k
' 2009.07.13 -- Add fnWait call

'----------------------------------------------------------*
On Error GoTo ErrorExit
Dim oHTML As New HTMLDocument
Set oDoc = oHTML.createDocumentFromUrl(pURL, vbNullString)
Do: DoEvents: Loop Until oDoc.ReadyState = "complete"
Call fnWait(2) ' Wait for JavaScript to run on page?
RCHGetURLData3 = oDoc.documentElement.outerHTML
Exit Function
ErrorExit:
RCHGetURLData3 = vError
End Function

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
' 2014.05.24 -- Add CSV output for logging of data requests
' 2014.05.25 -- Add double quotes around URL

'----------------------------------------------------------*

Dim i1 As Integer
Dim tStart As Single, tEnd As Single, dDate As Date
dDate = Now
tStart = Timer

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 sLog = "Y" Then
tEnd = Timer
i1 = FreeFile()
Open ThisWorkbook.Path & "\smf-log.csv" For Append As #i1
Print #i1, dDate & "," & (tEnd - tStart) & ",""" & Left(pURL, 150) &
""""
Close #i1
End If

End Function

Public Function smfGetWebPage(ByVal pURL As String, _
Optional ByVal pUseIE As Integer = 0, _
Optional ByVal pConvType As Integer = 0) As String

'----------------------------------------------------------*
' 2011.02.16 -- Add routine
' 2011.04.27 -- Add HTML codes 0 thru 9

'----------------------------------------------------------*
For iData = 1 To kPages
If aData(iData, 1) = pUseIE & ":" & pURL Then
If sWebCache <> "N" Then
' Retrieve page from local cache
smfGetWebPage = aData(iData, 2)
Exit Function
End If

' Else, the cache is (temporarily) disabled. Clear the
cache entry and fall through
aData(iData, 1) = ""
aData(iData, 2) = ""
End If

If aData(iData, 1) = "" Then
s2 = RCHGetURLData(pURL, pUseIE)
Select Case pConvType
Case 0
s2 = Replace(s2, "&", "&")
s2 = Replace(s2, " <b>", "<b> ")
s2 = Replace(s2, " ", " ")
s2 = Replace(s2, Chr(9), " ")
s2 = Replace(s2, Chr(10), "")
s2 = Replace(s2, Chr(13), "")
s2 = Replace(s2, "0", "0")
s2 = Replace(s2, "1", "1")
s2 = Replace(s2, "2", "2")
s2 = Replace(s2, "3", "3")
s2 = Replace(s2, "4", "4")
s2 = Replace(s2, "5", "5")
s2 = Replace(s2, "6", "6")
s2 = Replace(s2, "7", "7")
s2 = Replace(s2, "8", "8")
s2 = Replace(s2, "9", "9")
s2 = Replace(s2, "–", Chr(150))
s2 = Replace(s2, "—", "-")
s2 = Replace(s2, "—", "-")
s2 = Replace(s2, " ", " ")
s2 = Replace(s2, Chr(160), " ")
s2 = Replace(s2, "<TH", "<td")
s2 = Replace(s2, "</TH", "</td")
s2 = Replace(s2, "<th", "<td")
s2 = Replace(s2, "</th", "</td")
Case 1
s2 = Replace(s2, Chr(10), Chr(13))
End Select
Select Case pURL
Case "http://finance.yahoo.com/advances"
s2 = Replace(s2, "<sup>1</sup>", "")
End Select
aData(iData, 1) = pUseIE & ":" & pURL
aData(iData, 2) = s2
smfGetWebPage = s2
Exit Function
End If

If iData = kPages Then
smfGetWebPage = "Error -- Too many web page retrievals"
Exit Function
End If
Next iData
smfGetWebPage = "Error"
End Function

Public Function smfGetAData(p1 As Integer, p2 As Integer)
smfGetAData = Left(aData(p1, p2), 32767)
End Function
Public Sub smfFixLinks()

'----------------------------------------------------------*
' 2012.01.02 -- Expand to do all sheets in workbook

'----------------------------------------------------------*
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Cells.Replace _
What:="'*\RCH_Stock_Market_Functions.xla'!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next Sht
End Sub

Function IfError(formula As Variant, show As String)

On Error GoTo ErrorHandler

If IsError(formula) Then
IfError = show
Else
IfError = formula
End If

Exit Function

ErrorHandler:
Resume Next

End Function

Public Function fnWait(iSeconds As Integer)
Dim varStart As Variant
varStart = Timer
Do While Timer < varStart + iSeconds
DoEvents
Loop
End Function
Function smfStrExtr(pString As String, pStart As String, pEnd As String)

'----------------------------------------------------------*
' 2010.01.22 -- Add function
' 2010.06.06 -- Add error checking
' 2011.07.12 -- Add dummy characters to represent start and end of
input string

'----------------------------------------------------------*
If pStart = "~" Then
iPos1 = 1
iPos3 = 2
Else
iPos1 = InStr(pString, pStart) + Len(pStart)
iPos3 = iPos1
If iPos1 = Len(pStart) Then
smfStrExtr = ""
Exit Function
End If
End If
If pEnd = "~" Then iPos2 = Len(pString) + 1 Else iPos2 = InStr(iPos3,
pString, pEnd)
If iPos2 = 0 Then
smfStrExtr = ""
Exit Function
End If
smfStrExtr = Mid(pString, iPos1, iPos2 - iPos1)
End Function
Function smfEval(pData As String)

'----------------------------------------------------------*
' 2012.01.21 -- Add function

'----------------------------------------------------------*
smfEval = "Error"
smfEval = Evaluate(pData)
End Function
Function smfJoin(myRange As Range, myDelimiter As String)

'----------------------------------------------------------*
' 2011.02.16 -- Add function

'----------------------------------------------------------*
smfJoin = ""
For Each oCell In myRange
If smfJoin <> "" And oCell.Value <> "" Then smfJoin = smfJoin &
myDelimiter
smfJoin = smfJoin & oCell.Value
Next oCell
End Function
Public Function smfWord(ByVal Haystack As String, _
ByVal Occurrence As Long, _
Optional ByVal Delimiter As String = " ") As String

'----------------------------------------------------------*
' 2011.02.16 -- Add function

'----------------------------------------------------------*
On Error GoTo ErrorHandler
smfWord = Split(Haystack, Delimiter)(Occurrence - 1)
Exit Function
ErrorHandler:
smfWord = ""
End Function
Public Function smfStripHTML(ByVal sHTML As String) As String

'----------------------------------------------------------*
' 2014.04.07 -- Add function

'----------------------------------------------------------*
Dim oDoc As HTMLDocument
Set oDoc = New HTMLDocument
oDoc.body.innerHTML = sHTML
smfStripHTML = oDoc.body.innerText
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
==================================

Thu May 29, 2014 12:53 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but I just don't see this as a reasonable way to address the issue.

If I were to address the situation, I'd rather have something that would go
in and erase the cached web page that is causing the problem. That would
automatically have the web page refreshed, for all items from that web
page, with a Ctrl-Alt-F9 keyboard initiated recalculation. But the only
situations that could be identified by the add-in would be those that had
"Error" because the web page couldn't be retrieved. It couldn't identify
the situations where a web page was returned by the web site, but didn't
contain the necessary data.

Another way to address it is with the smfUpdateDownloadTable process, since
that puts values into the workbook. Only the line(s) with error values
would need to be redone. And, not by coincidence, a new change to that
process now allows you to highlight any range, column, or row and
selectively update it. Although you'd want to run smfForceRecalculation
first, to clear out all saved web pages. But that wouldn't affect the
already retrieved items, since they don't recalculate, and don't cause
their web pages to be retrieved again.

On Wed, May 28, 2014 at 8:58 PM, andrei radulescu-banu bitdribble@... wrote:

> Here is another idea for a small modification. My spreadsheet has now
> ~70 symbols, and sometimes the referring web page for just one ticker among
> all is temporarily failing. This causes all computations depending on that
> cell to give error.
>
> One can always execute the smfForceRecalculate() macro (saved in a top
> toolbar button), but this causes all other 69 symbols to reload, and now
> another symbol may fail to load one of the referring web pages.
>

Does it happen that often?

I heard about it a bit when RCHGetYahooQuotes() was touchy, but the easiest
solution there was to array-enter the formula and make several hundred
requests into a single request. And this method wouldn't have solved that
anyway, because RCHGetYahooQuotes() doesn't even "cache" the web page.

> As a workaround, here is a small modification to modUtilities: two new
> macros, one disabling the page cache, one enabling it. The cache is by
> default enabled. When a cell does not load, I can disable the cache, then
> click and type 'enter&#39; just on that cell, then reenable the cache.
>
> One pitfall: if several cells depend on the same flaky URL, one has to
> click and type 'enter&#39; on all of them. But, depending on the sheet layout,
> it's usually pretty obvious that the cell needs to be reloaded.
>

The whole point of that caching is so that multiple items can be retrieved
from a web page with a single Internet access of the web page.

There may be cases where it's obvious something needs to be fixed. But that
fix may be more than just retrieving the web page again, and may require
some investigation (i.e. web site is down).

Another alternative, not coded here, is to implement a retry mechanism in
> the RCHGetURLData() routine for the case when we get errors (assuming
> there's a way to tell programmatically that the page is not fully loaded -
> which requires further investigation, and may not be possible).
>

Sometimes the error is due to a timeout. Now, with the timing on the
retrieval, the routine would have the ability to tell if it returned an
error quickly or not. But if the add-in couldn't retrieve the web page,
it's unlikely that it will be available a split second later. It's most
often a temporary glitch. Which might mean a five minute wait is necessary.
Diposting oleh EXCEL FEED LEARNING di 04.32

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)