4 Messages
Digest #4019
Messages
Sat May 20, 2017 5:19 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
See the 50-day average example I gave here:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
It might be quicker to get the 50-day moving average from some sort of
screener. For example, Barchart has a free screener and you can download 10
results per day (limited to the first 1000 stocks). They have a number of
performance fields, including the 50-day average. For example:
*Symbol* *Name* *Last* *50D MA* *50D MA* *50D MA Str* *50D MA Dir*
*50D Chg* *50D
%Chg* *1M %Chg* *3M %Chg* *6M %Chg* *9M %Chg* *52W %Chg*
AGFS Agrofresh Solutions 6.99 4.99 Buy Maximum Weakening 4.17 147.87% 20.73%
156.04% 185.31% 16.89% 55.33%
ANGI Angie's List Inc 11.24 7.23 Buy Strong Weakest 5.78 105.86% 92.47%
95.14% 27.29% 15.88% 32.08%
COOL Majesco Entertainmnt 13.44 11.45 Buy Weak Strongest 6.75 100.90% 2.52%
140.86% 304.82% 267.21% 146.15%
CONN Conn's Inc 15.8 12.8 Buy Average Weakest 7.8 97.50% 19.25% 54.15%
46.98% 127.01% 45.89%
CORI Corium Intl Cmn 6.85 4.5 Buy Maximum Strengthening 3.16 85.64% 48.59%
78.39% 34.31% 21.67% 84.14%
CYAD Celyad Sa Ads 40.58 29.54 Buy Average Strengthening 18.37 82.71% 41.15%
97.18% 120.54% 59.70% -18.84%
It might be quicker to download a few screen results and then refer to
them using VLOOKUP() functions...
Just checked. If I'm logged into my free BarChart account, the screening
result can be read in with the smfGetCSVFile() function.
On Sat, May 20, 2017 at 3:18 PM, lewglenn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I know you've been dealing with this problem for several weeks now thanks
> to Yahoo's latest actions. I've downloaded the latest SMFAdd-In. Can you
> please tell me what I need to do to replace the RCHGetYahooHistory call in
> the following coding (to get 50-day MA):
>
> For r = 2 To rowmax
> If IsEmpty(Cells(r, "P")) Then
> Res = Cells(r, "A")
> Cells(r, "I") = Application.WorksheetFunction.
> Average(RCHGetYahooHistory(Res, , , , , , , , "a", 0, , , 50, 1))
> End If
> Next r
>
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
It might be quicker to get the 50-day moving average from some sort of
screener. For example, Barchart has a free screener and you can download 10
results per day (limited to the first 1000 stocks). They have a number of
performance fields, including the 50-day average. For example:
*Symbol* *Name* *Last* *50D MA* *50D MA* *50D MA Str* *50D MA Dir*
*50D Chg* *50D
%Chg* *1M %Chg* *3M %Chg* *6M %Chg* *9M %Chg* *52W %Chg*
AGFS Agrofresh Solutions 6.99 4.99 Buy Maximum Weakening 4.17 147.87% 20.73%
156.04% 185.31% 16.89% 55.33%
ANGI Angie's List Inc 11.24 7.23 Buy Strong Weakest 5.78 105.86% 92.47%
95.14% 27.29% 15.88% 32.08%
COOL Majesco Entertainmnt 13.44 11.45 Buy Weak Strongest 6.75 100.90% 2.52%
140.86% 304.82% 267.21% 146.15%
CONN Conn's Inc 15.8 12.8 Buy Average Weakest 7.8 97.50% 19.25% 54.15%
46.98% 127.01% 45.89%
CORI Corium Intl Cmn 6.85 4.5 Buy Maximum Strengthening 3.16 85.64% 48.59%
78.39% 34.31% 21.67% 84.14%
CYAD Celyad Sa Ads 40.58 29.54 Buy Average Strengthening 18.37 82.71% 41.15%
97.18% 120.54% 59.70% -18.84%
It might be quicker to download a few screen results and then refer to
them using VLOOKUP() functions...
Just checked. If I'm logged into my free BarChart account, the screening
result can be read in with the smfGetCSVFile() function.
On Sat, May 20, 2017 at 3:18 PM, lewglenn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I know you've been dealing with this problem for several weeks now thanks
> to Yahoo's latest actions. I've downloaded the latest SMFAdd-In. Can you
> please tell me what I need to do to replace the RCHGetYahooHistory call in
> the following coding (to get 50-day MA):
>
> For r = 2 To rowmax
> If IsEmpty(Cells(
> Res = Cells(r, "A"
> Cells(r, "I"
> Average(RCHGetYahoo
> End If
> Next r
>
Sat May 20, 2017 5:30 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I will just create the # of rows based on number of calendar days. A few
extra rows shouldn't matter. If there are more rows than the data range can
fill, the function will just leave them blank.
When calling from VBA, there are no active data rows relevant to the
function.
On Sat, May 20, 2017 at 4:41 PM, earladamy@
...wrote:
>
> > I just added code to default them if no range was used for the
> function, to the difference between the two dates and the size of the
> pItems parameter. That may be larger than needed, but it will just use what
> it needs when returning data.
>
> Just to clarify (when calling from VBA) are you resizing the returned
> array to match the number of active data rows?
>
> When I moved to processing CSV data, the split() function provided a
> properly sized array so I was able to dispense with processing the empty
> rows in my model. In implementing the new smf function, I pass a row
> estimate based on ((((calendar days) / 5) * 7) *1.1). When the array is
> returned, I size a new array and move the data into it. That would be
> duplicative if you are already doing (or planning on doing) that.
>
>
>
extra rows shouldn'
fill, the function will just leave them blank.
When calling from VBA, there are no active data rows relevant to the
function.
On Sat, May 20, 2017 at 4:41 PM, earladamy@
...wrote:
>
> > I just added code to default them if no range was used for the
> function, to the difference between the two dates and the size of the
> pItems parameter. That may be larger than needed, but it will just use what
> it needs when returning data.
>
> Just to clarify (when calling from VBA) are you resizing the returned
> array to match the number of active data rows?
>
> When I moved to processing CSV data, the split() function provided a
> properly sized array so I was able to dispense with processing the empty
> rows in my model. In implementing the new smf function, I pass a row
> estimate based on ((((calendar days) / 5) * 7) *1.1). When the array is
> returned, I size a new array and move the data into it. That would be
> duplicative if you are already doing (or planning on doing) that.
>
>
>
Sat May 20, 2017 5:33 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't see any advantage. If you have to call to fill in, you may as well
just call to get everything. The biggest disadvantage of the current call
is that the web page is huge. But I immediately trim it down when I process
it to just the JSON data. Which is about a tenth of the page (and still 10
to 20 times larger than the CSV file was).
On Sat, May 20, 2017 at 3:38 PM, tamurphy@cableone.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Your idea is what prompted my question. Having gathered historical closing
> prices for a large range of dates, I'm casting about for an efficient way
> to append prices for a potentially variable number of succeeding dates. I
> suppose the problem arises from the fact that the historical price
> retrieval formula is array-entered, thereby fixing the date and cell
> ranges. A workaround might be to copy values only (sans formulas) to
> another range, as you suggest; then adjust the starting date of the array
> formula so that it acquires only missing subsequent closes; and finally
> appending the newly retrieved data to the saved range. Sounds pretty
> kludgy, but it would likely work. Thoughts?
>
>
>
just call to get everything. The biggest disadvantage of the current call
is that the web page is huge. But I immediately trim it down when I process
it to just the JSON data. Which is about a tenth of the page (and still 10
to 20 times larger than the CSV file was).
On Sat, May 20, 2017 at 3:38 PM, tamurphy@cableone.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Your idea is what prompted my question. Having gathered historical closing
> prices for a large range of dates, I'm casting about for an efficient way
> to append prices for a potentially variable number of succeeding dates. I
> suppose the problem arises from the fact that the historical price
> retrieval formula is array-entered, thereby fixing the date and cell
> ranges. A workaround might be to copy values only (sans formulas) to
> another range, as you suggest; then adjust the starting date of the array
> formula so that it acquires only missing subsequent closes; and finally
> appending the newly retrieved data to the saved range. Sounds pretty
> kludgy, but it would likely work. Thoughts?
>
>
>
Sat May 20, 2017 7:26 pm (PDT) . Posted by:
"Marc Johnson" marclerjohnson
I use VBA to perform delta downloads to minimize network traffic and Excel calculation time. (I try to be a very parsimonious netizen.) If a split occurs, I "call to get everything", then resume delta downloads.
If you're a VBA coder and want to try delta downloads, below is an example code snippet. (Note – It will not execute as written. This is for the old Yahoo API, not the new. Customize it to suit yourself. You'll need to add code to handle rolling December 31 over to the next year. Use at your own risk.)
In general, most people should use box-stock SMF_Addin!!! (Thank you, Randy, for an awesome tool!) But if anyone wants to experiment with appending delta price history . . .
'Two worksheets are used, one named strStockSymbol, the other "TempYahoo".
datLastPrice = WB.Worksheets(strStockSymbol).Range("A2").Value
strStartDay = Format(Day(datLastPrice) + 1, "00")
strStartMonth = Format(Month(datLastPrice) - 1, "00")
strStartYear = Format(Year(datLastPrice), "00")
Select Case Val(strStartDay)
Case 29 'Handle leap year
If Val(strStartMonth) = 1 And Not Val(strStartYear) Mod 4 = 0 Then
strStartDay = "01"
strStartMonth = "02"
End If
Case 30
If Val(strStartMonth) = 1 Then
strStartDay = "01"
strStartMonth = "02"
End If
Case 31
If (Val(strStartMonth) = 3 Or Val(strStartMonth) = 5 Or Val(strStartMonth) = 8 Or Val(strStartMonth) = 10) Then
strStartDay = "01"
strStartMonth = CStr(Val(strStartMonth)) + 1
End If
Case 32
strStartDay = "01"
strStartMonth = CStr(Val(strStartMonth)) + 1
End Select
datToday = Now()
strEndDay = Format(Day(datToday), "00")
strEndMonth = Format(Month(datToday), "00")
strEndYear = Format(Year(datToday), "00")
strDownloadOHLCV_URL = "URL;http://table.finance.yahoo.com/table.csv?s=" + strStockSymbol + "&a=" + strStartMonth + "&b=" + strStartDay + "&c=" + strStartYear + "&d=" + strEndMonth + "&e=" + strEndDay + "&f=" + strEndYear + "&g=d&ignore=.csv"
With ActiveSheet.QueryTables.Add(Connection:=strDownloadOHLCV_URL, Destination:=Range("$A$1"))
'Add standard boilerplate code here, per normal QueryTables.Add syntax, to put the data in worksheet "TempYahoo".
'Copy temp data to existing worksheet.
lngRowNumber = WB.Worksheets("TempYahoo").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
Rows(WB.Worksheets("TempYahoo").Range("A2").Row & ":" & WB.Worksheets("TempYahoo").Range("A" & CStr(lngRowNumber)).Row).Copy
WB.Worksheets(strStockSymbol).Rows(2).Insert (xlShiftDown)
WB.Worksheets("TempYahoo").Cells.Clear
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: May 20, 2017 6:34 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Update on Yahoo Historical Quotes
I don't see any advantage. If you have to call to fill in, you may as well just call to get everything. The biggest disadvantage of the current call is that the web page is huge. But I immediately trim it down when I process it to just the JSON data. Which is about a tenth of the page (and still 10 to 20 times larger than the CSV file was).
On Sat, May 20, 2017 at 3:38 PM, tamurphy@cableone.net <mailto:tamurphy@cableone.net> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:
Your idea is what prompted my question. Having gathered historical closing prices for a large range of dates, I'm casting about for an efficient way to append prices for a potentially variable number of succeeding dates. I suppose the problem arises from the fact that the historical price retrieval formula is array-entered, thereby fixing the date and cell ranges. A workaround might be to copy values only (sans formulas) to another range, as you suggest; then adjust the starting date of the array formula so that it acquires only missing subsequent closes; and finally appending the newly retrieved data to the saved range. Sounds pretty kludgy, but it would likely work. Thoughts?
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
If you're a VBA coder and want to try delta downloads, below is an example code snippet. (Note – It will not execute as written. This is for the old Yahoo API, not the new. Customize it to suit yourself. You'll need to add code to handle rolling December 31 over to the next year. Use at your own risk.)
In general, most people should use box-stock SMF_Addin!!! (Thank you, Randy, for an awesome tool!) But if anyone wants to experiment with appending delta price history . . .
'Two worksheets are used, one named strStockSymbol, the other "TempYahoo".
datLastPrice = WB.Worksheets(strStockSymbol).Range("A2").Value
strStartDay = Format(Day(datLastPrice) + 1, "00")
strStartMonth = Format(Month(datLastPrice) - 1, "00")
strStartYear = Format(Year(datLastPrice), "00")
Select Case Val(strStartDay)
Case 29 'Handle leap year
If Val(strStartMonth) = 1 And Not Val(strStartYear) Mod 4 = 0 Then
strStartDay = "01"
strStartMonth = "02"
End If
Case 30
If Val(strStartMonth) = 1 Then
strStartDay = "01"
strStartMonth = "02"
End If
Case 31
If (Val(strStartMonth) = 3 Or Val(strStartMonth) = 5 Or Val(strStartMonth) = 8 Or Val(strStartMonth) = 10) Then
strStartDay = "01"
strStartMonth = CStr(Val(strStartMonth)) + 1
End If
Case 32
strStartDay = "01"
strStartMonth = CStr(Val(strStartMonth)) + 1
End Select
datToday = Now()
strEndDay = Format(Day(datToday), "00")
strEndMonth = Format(Month(datToday), "00")
strEndYear = Format(Year(datToday), "00")
strDownloadOHLCV_URL = "URL;http://table.finance.yahoo.com/table.csv?s=" + strStockSymbol + "&a=" + strStartMonth + "&b=" + strStartDay + "&c=" + strStartYear + "&d=" + strEndMonth + "&e=" + strEndDay + "&f=" + strEndYear + "&g=d&ignore=.csv"
With ActiveSheet.QueryTables.Add(Connection:=strDownloadOHLCV_URL, Destination:=Range("$A$1"))
'Add standard boilerplate code here, per normal QueryTables.Add syntax, to put the data in worksheet "TempYahoo".
'Copy temp data to existing worksheet.
lngRowNumber = WB.Worksheets("TempYahoo").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
Rows(WB.Worksheets("TempYahoo").Range("A2").Row & ":" & WB.Worksheets("TempYahoo").Range("A" & CStr(lngRowNumber)).Row).Copy
WB.Worksheets(strStockSymbol).Rows(2).Insert (xlShiftDown)
WB.Worksheets("TempYahoo").Cells.Clear
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: May 20, 2017 6:34 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Update on Yahoo Historical Quotes
I don't see any advantage. If you have to call to fill in, you may as well just call to get everything. The biggest disadvantage of the current call is that the web page is huge. But I immediately trim it down when I process it to just the JSON data. Which is about a tenth of the page (and still 10 to 20 times larger than the CSV file was).
On Sat, May 20, 2017 at 3:38 PM, tamurphy@cableone.net <mailto:tamurphy@cableone.net> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:
Your idea is what prompted my question. Having gathered historical closing prices for a large range of dates, I'm casting about for an efficient way to append prices for a potentially variable number of succeeding dates. I suppose the problem arises from the fact that the historical price retrieval formula is array-entered, thereby fixing the date and cell ranges. A workaround might be to copy values only (sans formulas) to another range, as you suggest; then adjust the starting date of the array formula so that it acquires only missing subsequent closes; and finally appending the newly retrieved data to the saved range. Sounds pretty kludgy, but it would likely work. Thoughts?
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar