Sabtu, 09 Januari 2016

[smf_addin] Digest Number 3603[3 Attachments]

15 Messages

Digest #3603
1a
1b
Re: Block Data Download by "Randy Harmelink" rharmelink
2a
2b
Re: Index Highs and Lows by "Randy Harmelink" rharmelink
3a
3b
5b
Re: Download IBD Screens in Excel via VBA? by "Randy Harmelink" rharmelink

Messages

Sat Jan 9, 2016 7:20 am (PST) . Posted by:

rjemery7

Is there a way to download as one large block of data the Date, Open, High, Low, Close, Volume, Adj Close for the DJ Transportation Average from 1/1/2007 to Current Date?

If not, how could all that data be best obtained with a minimum of interaction?

I also wish to do the same with the DJI, DJU, S&P 500/600/400/1500, NASDAQ 100/COMP, Russell 2000 and other Russell indexes. Any special issues concerning those?


Sat Jan 9, 2016 7:26 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Why wouldn't it just be a single RCHGetYahooHistory() function for each
index?

On Sat, Jan 9, 2016 at 8:20 AM, rjemery@... wrote:

>
> Is there a way to download as one large block of data the Date, Open,
> High, Low, Close, Volume, Adj Close for the DJ Transportation Average from
> 1/1/2007 to Current Date?
>
> If not, how could all that data be best obtained with a minimum of
> interaction?
>
> I also wish to do the same with the DJI, DJU, S&P 500/600/400/1500, NASDAQ
> 100/COMP, Russell 2000 and other Russell indexes. Any special issues
> concerning those?
>

Sat Jan 9, 2016 7:22 am (PST) . Posted by:

rjemery7

For each of the years from 2007 onward, how could I best obtain the high and low for the various indexes, both interday and at the close?

Sat Jan 9, 2016 7:27 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Just use RCHGetYahooHistory() to grab the full range of historical quotes,
than generate those statistics yourself from that data...

On Sat, Jan 9, 2016 at 8:22 AM, rjemery@... wrote:

>
> For each of the years from 2007 onward, how could I best obtain the high
> and low for the various indexes, both interday and at the close?
>

Sat Jan 9, 2016 7:26 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

You might want to look at this site for split history. I like it over yahoo. http://getsplithistory.com/IBM

You have to enter the stock symbol in caps or it won't work


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, January 06, 2016 9:53 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Retrieving Yahoo stock split and dividend info


How about getting split info from MorningStar:

http://performance.morningstar.com/Performance/stock/split-history.action?t=MMM

I have no idea of their coverage on foreign stocks. As far as I know, the only way to get all the splits from Yahoo would be to retrieve every page of historical quotes and looks for them. Not a good process. I used to get it from the basic charts page, but Yahoo doesn't have that any more.
Hmmm. The non-US sites still have the basic charts. For now, anyway. For example:

https://uk.finance.yahoo.com/q/bc?s=0138.KL
Note the splits just under the chart. They can be retrieved with this in cell C1, and copy down:

=smfGetTagContent("https://uk.finance.yahoo.com/q/bc?s=0138.KL","nobr",ROWS(C$1:C1),"Splits:")

On Wed, Jan 6, 2016 at 6:48 PM, mark.boogaers@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

Anyone who could assist with this. I am looking to retrieve stock split and dividend info for stocks in my portfolio automatically from Yahoo.

With B7 being my purchase date and J4 the ticker.

Using
=RCHGetYahooHistory(J4,YEAR($B7),MONTH($B7),DAY($B7),YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()),"v","C",0,2,1)
to get the dividend date. I am guessing this gives the last dividend date in case of multiple.

Then using something I found in another topic to get the total dividend.
=SUM(INDEX(RCHGetYahooHistory(J4,YEAR($B7),MONTH($B7),DAY($B7),YEAR($B16),MONTH($B16),DAY($B16),"v",,0,,,20,2),,2))
I dont really understand how this works, but it does work.(B16 has the last trade date of the stock)

I am trying to get same for stock splits. Anyone any idea? I tried a way around direct retrieval by using the adjusted vs closing prices, but that is not accurate enough.

Yahoo's page for this stock is 0138.KL Historical Prices | MY E.G. SERVICES BHD Stock - Yahoo! Finance <http://finance.yahoo.com/q/hp?s=0138.KL+Historical+Prices>


Sat Jan 9, 2016 7:30 am (PST) . Posted by:

"Randy Harmelink" rharmelink

They only cover most of NYSE, AMEX, and NASDAQ issues, not foreign stocks...

On Sat, Jan 9, 2016 at 8:26 AM, 'Kermit W. Prather' kermitp@... wrote:

>
> You might want to look at this site for split history. I like it over
> yahoo. http://getsplithistory.com/IBM
>
>
>
> You have to enter the stock symbol in caps or it won't work
>

Sat Jan 9, 2016 7:42 am (PST) . Posted by:

rjemery7

For the DJ Transpiration Average, can you offer any insight why Yahoo Finance reports index volume differently from Google Finance?

For 1/8/2016,
Yahoo shows it as 4,664,940,000
while Google lists it as 21,507,084.

That's quite some difference.


Sat Jan 9, 2016 9:22 am (PST) . Posted by:

"Randy Harmelink" rharmelink

I'm not even sure what volume on an index means...

On Sat, Jan 9, 2016 at 8:42 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> For the DJ Transpiration Average, can you offer any insight why Yahoo
> Finance reports index volume differently from Google Finance?
>
> For 1/8/2016,
> Yahoo shows it as 4,664,940,000
> while Google lists it as 21,507,084.
>
> That's quite some difference.
>

Sat Jan 9, 2016 5:15 pm (PST) . Posted by:

brad.reel

Hi Randy. Long time, no talk and Happy New Year. Hope 2016 treats you well.


Have a problem I am hoping you might be able to help me with. I had some VBA code written that would pull down from the investor's.com site the various IBD screens in excel format. Sometime back, this code quit working when IBD did one of their code re-writes. I've managed to cobble something together that works, but it's pretty ugly. Hoping you might know a better way.


This piece of code use to work, I could send this URL as part of an HTTP get and it would pull down the screen in Excel format:

'myURL(1) = "http://research.investors.com/screencenter/export.aspx?ScreenID=" & IBD50Code & "&Type=1&exportType=excel"


The IBD50Code is a variable because IBD used to rotate the codes on the URL daily. I don't think they rotate them anymore, but I haven't really been paying attention of late.


Once that URL stopped working, I was able to make this work:
'myURL(1) = "http://research.investors.com/screen-center/?screen=" & IBD50Code & ""



This will pull down the webpage as an excel file, which I can then open and pull the symbols out of. It works, but it's a mess because while running it throws all kinds of errors because I'm opening a web page with an Excel extension.


Here's the code I run to execute the URL's. I believe it's fairly standard, but maybe there's a problem in there.


j = 1
For Each i In myURL
WinHttpReq.Open "GET", i, False
WinHttpReq.Send

Path = Directory + Filename(j)

i = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile (Path)
oStream.Close
End If
j = j + 1

Next


I have dug through the web pages trying to figure out a URL I could call, but I'm not having any luck. Part of the problem is that I know just enough HTML programming to be dangerous. :)




If you have any ideas on what I VBA code I could execute to be able to download the actual excel files for the IBD screens I would be most appreciative. If you don't want to mess with it, I understand that also, but figured it didn't hurt to ask.


Thanks in advance, Randy.

Sat Jan 9, 2016 6:18 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Coincidentally, I'm just starting to look at downloading a few files using
VBA. I didn't like the method that you're using, because it reads it into
VBA and then writes it. I want to download it directly. I'll have to,
because some of my files are VERY large.

I tried the technique described on these two web pages, which uses a
Windows API for downloading::

https://social.msdn.microsoft.com/Forums/en-US/beb6fa0e-fbc8-49df-9f2e-30f85d941fad/download-file-from-ie-with-vba
http://officetricks.com/download-file-from-website-using-excel/

In its simplest version, this test worked fine for me:

Declare 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 Sample_Download()
Const sURL = "
http://www.dripinvesting.org/Tools/U.S.DividendChampions.xls"
Const sFile = "C:\Users\Randy\Documents\Temp\U.S.DividendChampions.xls"
MsgBox (URLDownloadToFile(0, sURL, sFile, 0, 0))
End Sub

But I knew the URL of the EXCEL file. I don't know if you can use the URL
you know or not. To see if anything else is going on, you could use some
type of URL sniffer. I use the HTTPFox extension with FireFox. Or, if using
FireFox, you can go to your download dropdown and copy the URL if the
downloaded file? I've done that a time or two.

On Sat, Jan 9, 2016 at 6:15 PM, brad.reel@... wrote:

> Hi Randy. Long time, no talk and Happy New Year. Hope 2016 treats you
> well.
>
> Have a problem I am hoping you might be able to help me with. I had some
> VBA code written that would pull down from the investor's.com site the
> various IBD screens in excel format. Sometime back, this code quit working
> when IBD did one of their code re-writes. I've managed to cobble something
> together that works, but it's pretty ugly. Hoping you might know a better
> way.
>
> This piece of code use to work, I could send this URL as part of an HTTP
> get and it would pull down the screen in Excel format:
>
> 'myURL(1) = "
> http://research.investors.com/screencenter/export.aspx?ScreenID=" &
> IBD50Code & "&Type=1&exportType=excel"
>
> The IBD50Code is a variable because IBD used to rotate the codes on the
> URL daily. I don't think they rotate them anymore, but I haven't really
> been paying attention of late.
>
> Once that URL stopped working, I was able to make this work:
> 'myURL(1) = "http://research.investors.com/screen-center/?screen=" &
> IBD50Code & ""
>
> This will pull down the webpage as an excel file, which I can then open
> and pull the symbols out of. It works, but it's a mess because while
> running it throws all kinds of errors because I'm opening a web page with
> an Excel extension.
>
> Here's the code I run to execute the URL's. I believe it's fairly
> standard, but maybe there's a problem in there.
>
> j = 1
> For Each i In myURL
> WinHttpReq.Open "GET", i, False
> WinHttpReq.Send
>
> Path = Directory + Filename(j)
>
> i = WinHttpReq.ResponseBody
> If WinHttpReq.Status = 200 Then
> Set oStream = CreateObject("ADODB.Stream")
> oStream.Open
> oStream.Type = 1
> oStream.Write WinHttpReq.ResponseBody
> oStream.SaveToFile (Path)
> oStream.Close
> End If
> j = j + 1
>
> Next
>
> I have dug through the web pages trying to figure out a URL I could call,
> but I'm not having any luck. Part of the problem is that I know just
> enough HTML programming to be dangerous. :)
>
> If you have any ideas on what I VBA code I could execute to be able to
> download the actual excel files for the IBD screens I would be most
> appreciative. If you don't want to mess with it, I understand that also,
> but figured it didn't hurt to ask.
>
>

Sat Jan 9, 2016 6:17 pm (PST) . Posted by:

"Gordon Pedersen"

Hi,

New to SMF, still following the tutorial at SeekingAlpha.

I noticed that the formulas I typed in have been changed by--I don't know
what. Is this the behavior I should expect? Is this ideal? (seems like
shorter formulas w/no more than what is strictly needed would be best)

The attached images show:

A) a formula as I typed it following the tutorial

C) the update dialog box that pops up every time I open this workbook I'm
playing around in (I pressed "Update")

C) the formula after it has been auto-updated

Gordon

--
Gordon Pedersen
Attachment(s) from Gordon Pedersen
3 of 3 File(s)

Sat Jan 9, 2016 6:33 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I'll assume the three items are (A), (B), and (C), since they are labeled
differently in the message and the attachments.

(B) and (C) should not be happening if you are saving and opening the
workbook on the same computer.

What happens is that when EXCEL saves a file with an add-in function
invocation, it appends the hard-coded location of the add-in (the prefix to
the function you are seeing in (C)). When EXCEL opens the file, if that
location matches what the add-in manager says is the location of the
add-in, it considers that "link" resolved and strips off the prefix, just
leaving the function invocation. If the saved hard-coded location of the
add-in doesn't match the location of the add-in from the add-in manager, it
considers the "link" to be unresolved and leaves the prefix on the function
invocation.

So, typically, if you save and the open the file, the saved hard-coded
add-in location SHOULD match the location that's stored in the add-in
manager, the "link" is resolved, and you never even know this process
occurred.

I know EXCEL has issues in this process with network locations, but your
add-in location is showing as on your C: drive. Are you perhaps saving the
EXCEL file onto a network drive, and opening it from there?

Because of this process, I often have issues when opening EXCEL workbook
from other people, because they chose to put the add-in in a different
location. I always tell EXCEL to not update any unresolved links, rather
than to update them. I wrote a macro to correct that. All it does is a
"Find and Replace" on any potential add-in related location prefixes and
change them to be nothing -- leaving just the function invocation. That
macro is smfFixLinks, and is within the add-in.

You did activate the add-in with the add-in manager? Right? Otherwise,
you'll always get the links.

On Sat, Jan 9, 2016 at 7:17 PM, Gordon Pedersen gordon@...wrote:

>
> Hi,
>
> New to SMF, still following the tutorial at SeekingAlpha.
>
> I noticed that the formulas I typed in have been changed by--I don't know
> what. Is this the behavior I should expect? Is this ideal? (seems like
> shorter formulas w/no more than what is strictly needed would be best)
>
> The attached images show:
>
> A) a formula as I typed it following the tutorial
>
> C) the update dialog box that pops up every time I open this workbook I'm
> playing around in (I pressed "Update")
>
> C) the formula after it has been auto-updated
>
>
>

Sat Jan 9, 2016 8:14 pm (PST) . Posted by:

"Gordon Pedersen"

Thanks, Randy. Given that using macros w/Excel, not just SMF, is new
territory, I'm almost surely making some basic mistake.

However, in the back of my mind, I also wonder if I might be caught in some
MS Office, Excel or Windows 7 security trap that I'm not aware of. (

I have Windows firewall running. I have not messed w/Excel or Office
default security settings, as far as I recall.)

Could be a little of both.

I'm going to start a fresh workbook, enter the same simple formulas using
JNJ as test symbol, and see what happens.

What I'd love is a short list of what to do, what Excel configuration items
to check/change, and what NOT to do. I'll start the list, knowing what I
propose will be wrong and imcomplete.

0. Install the latest SMF add-in in the default location: C:\SMF Add-in\

(I updated the new elements files from ogres-crypt: 1, 2, 3, 4, 5 6, and
9.)

1. Enable the add-in using instructions in SeekingAlpha tutorial

(tutorial URL:
http://seekingalpha.com/instablog/326478-derek-a-barrett/1414481-automating-alpha-getting-started-with-stock-market-functions-smf-add-in-for-excel
)

2. Start a brand-new worksheet and enter simple formulas like so in a row
with symbol in col A:

=RCHGetElementNumber($A2,13674)
=RCHGetElementNumber($A2,13666)
=RCHGetElementNumber($A2,2077) # this is the IBD formula

3. Create a valid login at investors.com (IBD) and prove that it works.

4. Use Excel Data / From web to log into investors.com using the following
URL (subbing in my investors.com email and password):

http://www.investors.com/Services/SiteAjaxService.asmx/MemberSingIn?strEmail=xxxxx&strPassword=yyyyy&blnRemember=True

5. Observe the formula definitions and values.

6. Save the file, close Excel.

7. Reopen Excel and open the file. Observe formula definitions and values.

8. ???

Gordon

On Sat, Jan 9, 2016 at 6:33 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> I'll assume the three items are (A), (B), and (C), since they are labeled
> differently in the message and the attachments.
>
> (B) and (C) should not be happening if you are saving and opening the
> workbook on the same computer.
>
> What happens is that when EXCEL saves a file with an add-in function
> invocation, it appends the hard-coded location of the add-in (the prefix to
> the function you are seeing in (C)). When EXCEL opens the file, if that
> location matches what the add-in manager says is the location of the
> add-in, it considers that "link" resolved and strips off the prefix, just
> leaving the function invocation. If the saved hard-coded location of the
> add-in doesn't match the location of the add-in from the add-in manager, it
> considers the "link" to be unresolved and leaves the prefix on the function
> invocation.
>
> So, typically, if you save and the open the file, the saved hard-coded
> add-in location SHOULD match the location that's stored in the add-in
> manager, the "link" is resolved, and you never even know this process
> occurred.
>
> I know EXCEL has issues in this process with network locations, but your
> add-in location is showing as on your C: drive. Are you perhaps saving the
> EXCEL file onto a network drive, and opening it from there?
>
> Because of this process, I often have issues when opening EXCEL workbook
> from other people, because they chose to put the add-in in a different
> location. I always tell EXCEL to not update any unresolved links, rather
> than to update them. I wrote a macro to correct that. All it does is a
> "Find and Replace" on any potential add-in related location prefixes and
> change them to be nothing -- leaving just the function invocation. That
> macro is smfFixLinks, and is within the add-in.
>
> You did activate the add-in with the add-in manager? Right? Otherwise,
> you'll always get the links.
>
> On Sat, Jan 9, 2016 at 7:17 PM, Gordon Pedersen gordon@...wrote:
>
>>
>> Hi,
>>
>> New to SMF, still following the tutorial at SeekingAlpha.
>>
>> I noticed that the formulas I typed in have been changed by--I don't know
>> what. Is this the behavior I should expect? Is this ideal? (seems like
>> shorter formulas w/no more than what is strictly needed would be best)
>>
>> The attached images show:
>>
>> A) a formula as I typed it following the tutorial
>>
>> C) the update dialog box that pops up every time I open this workbook I'm
>> playing around in (I pressed "Update")
>>
>> C) the formula after it has been auto-updated
>>
>>
>>
>
>

--
Gordon Pedersen

Sat Jan 9, 2016 8:29 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I'd add this formula to step 2, which should be the hard-coded version of
element #2077:

=RCHGetTableCell("http://research.investors.com/stock-checkup/xxxx-JNJ.aspx",1,">Debt/Equity
Ratio")

You could also try 5.5 -- run smfForceRecalculation to see if formula
results change (they should if you are now logged in). You shouldn't see
ANY changes in formula results immediately after logging in to IBD. The
add-in saves a copy of web pages internally before extracting data from
them. It won't be until you force the add-in to grab a fresh copy of the
web page from the Internet that you'll see values change, and that requires
either smfForceRecalculation or an exit and restart of EXCEL.

Note: I was just trying some of the formulas and getting really strange
behavior from EXCEL. Turns out the investors.com website is offline right
now. So make sure it's up and running before you try anything. :)

A firewall might complicate things. But if you're able to get to the
websites using the IE object from within EXCEL, the add-in should have
access to the Internet as well. It's just using the same MicroSoft API's
that IE is.

On Sat, Jan 9, 2016 at 9:14 PM, Gordon Pedersen gordon@...wrote:

>
> Thanks, Randy. Given that using macros w/Excel, not just SMF, is new
> territory, I'm almost surely making some basic mistake.
>
> However, in the back of my mind, I also wonder if I might be caught in
> some MS Office, Excel or Windows 7 security trap that I'm not aware of. (
>
> I have Windows firewall running. I have not messed w/Excel or Office
> default security settings, as far as I recall.)
>
> Could be a little of both.
>
> I'm going to start a fresh workbook, enter the same simple formulas using
> JNJ as test symbol, and see what happens.
>
> What I'd love is a short list of what to do, what Excel configuration
> items to check/change, and what NOT to do. I'll start the list, knowing
> what I propose will be wrong and imcomplete.
>
> 0. Install the latest SMF add-in in the default location: C:\SMF Add-in\
>
> (I updated the new elements files from ogres-crypt: 1, 2, 3, 4, 5 6, and
> 9.)
>
> 1. Enable the add-in using instructions in SeekingAlpha tutorial
>
> (tutorial URL:
> http://seekingalpha.com/instablog/326478-derek-a-barrett/1414481-automating-alpha-getting-started-with-stock-market-functions-smf-add-in-for-excel
> )
>
> 2. Start a brand-new worksheet and enter simple formulas like so in a row
> with symbol in col A:
>
> =RCHGetElementNumber($A2,13674)
> =RCHGetElementNumber($A2,13666)
> =RCHGetElementNumber($A2,2077) # this is the IBD formula
>
> 3. Create a valid login at investors.com (IBD) and prove that it works.
>
> 4. Use Excel Data / From web to log into investors.com using the
> following URL (subbing in my investors.com email and password):
>
>
> http://www.investors.com/Services/SiteAjaxService.asmx/MemberSingIn?strEmail=xxxxx&strPassword=yyyyy&blnRemember=True
>
> 5. Observe the formula definitions and values.
>
> 6. Save the file, close Excel.
>
> 7. Reopen Excel and open the file. Observe formula definitions and values.
>
> 8. ???
>
>
>

Sat Jan 9, 2016 8:34 pm (PST) . Posted by:

mark.boogaers

Hi,


Hope anyone can help with this. I am trying to get infom from website page as below using =RCHGetHTMLTable(URL,"",1,"",1) to get everything from the page which is only a data table page.


http://www.bursamarketplace.com/index.php?tpl=th001_fin_bs&stockcode=IQ8070734&ac=0 http://www.bursamarketplace.com/index.php?tpl=th001_fin_bs&stockcode=IQ8070734&ac=0



http://www.bursamarketplace.com/index.php?tpl=th001_fin_bs&stockcode=IQ8070734&ac=0

http://www.bursamarketplace.com/index.php?tpl=th001_fin_... http://www.bursamarketplace.com/index.php?tpl=th001_fin_bs&stockcode=IQ8070734&ac=0 myr (million) 12 Months 30 Jun 2011 12 Months 30 Jun 2012 12 Months 30 Jun 2013



View on www.bursamarketplac... http://www.bursamarketplace.com/index.php?tpl=th001_fin_bs&stockcode=IQ8070734&ac=0
Preview by Yahoo




This gets me all the data nicely upto the split in the table. Anything below "supplemental items" is not coming in.


Anyone who can assist please?


Mark






For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar