Sabtu, 09 Januari 2016

[smf_addin] Digest Number 3602

11 Messages

Digest #3602
1a
Re: Google data source not available anymore? by c2adf9311e2c386ea6b50154ae931d42
1b
Re: Google data source not available anymore? by c2adf9311e2c386ea6b50154ae931d42
1c
Re: Google data source not available anymore? by "Randy Harmelink" rharmelink
2a
Re: Brand new - IBD question by "Gordon Pedersen"
2b
Re: Brand new - IBD question by "Randy Harmelink" rharmelink
2c
Re: Brand new - IBD question by "Gordon Pedersen"
2d
Re: Brand new - IBD question by "Randy Harmelink" rharmelink
2e
Re: Brand new - IBD question by "Gordon Pedersen"
2f
Re: Brand new - IBD question by "Randy Harmelink" rharmelink
3a
Moving files between PCs by truebangalorean
3b
Re: Moving files between PCs by "Randy Harmelink" rharmelink

Messages

Fri Jan 8, 2016 8:36 am (PST) . Posted by:

c2adf9311e2c386ea6b50154ae931d42

After replacing the file in the folder where the add in is, the spreadsheet still shows errors. I also restarted Excel. Is there something I missed?

Fri Jan 8, 2016 10:11 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Some have reported that the AdvFN cross domains fix (see "Tips and FAQs"
web page) also fixed the old Google definitions, but I didn't think it
would be necessary after I changed the URLs, because it has to do with URL
redirection...

You aren't doing a ticker symbol where Google doesn't have financials, are
you?

What do you get with:

=RCHGetElementNumber("Version")

=RCHGetElementNumber("Definition",2742)

=smfGetTagContent("https://www.google.com/finance?fstype=ii&q=MMM","title")

=RCHGetElementNumber("MMM",2742)

=RCHGetTableCell("https://www.google.com/finance?fstype=ii&q=MMM",1,"INCINTERIMDIV","(except
for per share items)")

=RCHGetWebData("https://www.google.com/finance?fstype=ii&q=MMM",1,30)

On Fri, Jan 8, 2016 at 9:36 AM, sdglobe@... wrote:

>
> After replacing the file in the folder where the add in is, the
> spreadsheet still shows errors. I also restarted Excel. Is there something
> I missed?
>
>

Fri Jan 8, 2016 7:24 pm (PST) . Posted by:

"Gordon Pedersen"

Thanks, apologies for delay.

I went to ogres-crypt.com/SMF/works-in-progress/ page to get that "9" file
but today it is no longer there.

The "9" file at ogres-crypt.com/SMF/Elements/ was showing a moment ago with
a November, 2013, date and a size of 43685. Now I finally see the "9" file
of size 72796. Thanks for updating the page. I see that elements files
1-6 are also brand-new, but not yet downloaded them.

I downloaded element file "9" and now using your formula to display
definition, I get:

IBD;Debt/Equity Ratio;=RCHGetTableCell("
http://research.investors.com/stock-checkup/"&smfStrExtr(RCHGetWebData("
http://www.investors.com/search/searchresults.aspx?Ntt=~~~~~","/quotes/"),"/quotes/",".htm")&".aspx",1,">Debt/Equity
Ratio")

The formula I am testing with () still shows error.

It seemed from other posts that I would need have a login at investors.com.
I got partway in and they require me to give a street address so they can
send me a paper for some weeks. I don't like that and much prefer not to
do that; in fact, not even sure it is permitted at my current address.

So, bottom line, what, if anything, is required of me in that formula above?

Gordon

On Wed, Jan 6, 2016 at 11:02 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> Yes, it is correct to replace your local smf-elements-9.txt file with one
> from the Internet when they are updated.
>
> However, the web page listing was incorrect -- the file size is 72K. Not
> sure why you got 85K? I just made sure earlier today that all of the files
> on the "Elements" web page were up-to-date. I don't know if you got your
> file from there or from the "Works in Progress" web page (which should be
> correct, but I just removed it from there because it's now on the "Element"
> web page).
>
> You can check your definition with:
>
> =RCHGetElementNumber("Definition",2077)
>
> Right now, I get this:
>
> IBD;Debt/Equity Ratio;=RCHGetTableCell("
> http://research.investors.com/stock-checkup/xxxx-~~~~~.aspx",1,">Debt/Equity
> Ratio")
>
> Some sites, like investors.com, have security on them. In order for the
> add-in to be able to get to the data, you need to create an IE security
> cookie that EXCEL and the add-in can use to access the web site. For IBD,
> that means logging into the web site using either the EXCEL Web Query
> dialog, because they consider each form of browser or other tool as an
> individual connection. For example, if I'm using the web site with the
> add-in, and then visit the web site with my FireFox browser, I often need
> to log into the web site again.
>
> On Wed, Jan 6, 2016 at 9:38 PM, Gordon Pedersen gordon@...wrote:
>
>>
>> Just downloaded first time SMF Jan 5. Today playing w/tutorial at
>> http://seekingalpha.com/instablog/326478-derek-a-barrett/1414481-automating-alpha-getting-started-with-stock-market-functions-smf-add-in-for-excel
>> .
>>
>> My example Debt/Equity formula results in "Error". Formula is:
>> =RCHGetElementNumber(A2, 2077)​. It uses IBD and I'd rather learn how to
>> make ibd work sooner vs later.
>>
>> I see a new smf-elements-9.txt on ogres-crypt site. Size there is given
>> as 43685. The "9" file I got yesterday is size 85554.
>>
>> Is it right to download ogres-crypt file and replace my local file?
>>
>>
>>
>
>

--
Gordon Pedersen

Fri Jan 8, 2016 7:43 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

On Fri, Jan 8, 2016 at 8:24 PM, Gordon Pedersen gordon@...wrote:

>
> I went to ogres-crypt.com/SMF/works-in-progress/ page to get that "9"
> file but today it is no longer there.
>

*Correct. I have decided the element files on the "Elements" web page will
always be the current files, so there will no longer be a need to put them
on the "Works in Progress" web page.*

> The "9" file at ogres-crypt.com/SMF/Elements/ was showing a moment ago
> with a November, 2013, date and a size of 43685. Now I finally see the "9"
> file of size 72796. Thanks for updating the page. I see that elements
> files 1-6 are also brand-new, but not yet downloaded them.
>
> I downloaded element file "9" and now using your formula to display
> definition, I get:
>
> IBD;Debt/Equity Ratio;=RCHGetTableCell("
> http://research.investors.com/stock-checkup/"&smfStrExtr(RCHGetWebData("
> http://www.investors.com/search/searchresults.aspx?Ntt=~~~~~","/quotes/"),"/quotes/",".htm")&".aspx",1,">Debt/Equity
> Ratio")
>

*Your definition of element 2077 is still the old definition. Did you run
the smfForceRecalculation macro (which reloads the element definitions and
purges any saved copies of web pages) or exit and restart EXCEL (which
resets the VBA environment from scratch)?*

*Another pitfall others have had in the past is they have the add-in in
several different folders and updated the files in the wrong folder -- that
is, not the one pointed to by EXCEL's add-in manager. To double-check where
your add-in is located:*

*=RCHGetElementNumber("Version")*

*In addition to telling you which version of the add-in is running, it will
also tell you where it is located.*

> The formula I am testing with () still shows error.
>

*Yes. It would with that definition. You can also try the new formula that
is saved as the element
definition:=RCHGetTableCell("http://research.investors.com/stock-checkup/xxxx-MMM.aspx
<http://research.investors.com/stock-checkup/xxxx-MMM.aspx>",1,">Debt/Equity
Ratio")*

> It seemed from other posts that I would need have a login at investors.com.
> I got partway in and they require me to give a street address so they can
> send me a paper for some weeks. I don't like that and much prefer not to
> do that; in fact, not even sure it is permitted at my current address.
>

*The IBD data is proprietary and only available to website subscribers. I
haven't received the newspaper for years. Didn't even know it was required
to get a sample?*

*I don't know if there's any way around it. I currently have an annual
subscription with them, plus a monthly subscription for their e-Tables.*

> So, bottom line, what, if anything, is required of me in that formula
> above?
>

*See above. :)*

Fri Jan 8, 2016 8:02 pm (PST) . Posted by:

"Gordon Pedersen"

Thanks again.

I enabled and ran the recalc macro and now the definition shows:

IBD;Debt/Equity Ratio;=RCHGetTableCell("
http://research.investors.com/stock-checkup/xxxx-~~~~~.aspx",1,">Debt/Equity
Ratio")
I understand the web and programming well enough; not so used to Excel. I
just need to know what exactly to change and where precisely to change it.

File "9" has lots of "investors.com" lines. Many (all??) have the "
xxxx-~~~~~" I see above.

Do all those lines need to change? Is there a quick way to do it you've
already created? Otherwise, I can use my text editor.

Is the "xxxx" my ibd email address?

Is the "~~~~~" my ibd password?

What else needs to be done? (I'm probably missing something....)

Gordon

On Fri, Jan 8, 2016 at 7:43 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> On Fri, Jan 8, 2016 at 8:24 PM, Gordon Pedersen gordon@...wrote:
>
>>
>> I went to ogres-crypt.com/SMF/works-in-progress/ page to get that "9"
>> file but today it is no longer there.
>>
>
>
> *Correct. I have decided the element files on the "Elements" web page will
> always be the current files, so there will no longer be a need to put them
> on the "Works in Progress" web page.*
>
>
>
>> The "9" file at ogres-crypt.com/SMF/Elements/ was showing a moment ago
>> with a November, 2013, date and a size of 43685. Now I finally see the "9"
>> file of size 72796. Thanks for updating the page. I see that elements
>> files 1-6 are also brand-new, but not yet downloaded them.
>>
>> I downloaded element file "9" and now using your formula to display
>> definition, I get:
>>
>> IBD;Debt/Equity Ratio;=RCHGetTableCell("
>> http://research.investors.com/stock-checkup/"&smfStrExtr(RCHGetWebData("
>> http://www.investors.com/search/searchresults.aspx?Ntt=~~~~~","/quotes/"),"/quotes/",".htm")&".aspx",1,">Debt/Equity
>> Ratio")
>>
>
>
>
>
> *Your definition of element 2077 is still the old definition. Did you run
> the smfForceRecalculation macro (which reloads the element definitions and
> purges any saved copies of web pages) or exit and restart EXCEL (which
> resets the VBA environment from scratch)?*
>
>
> *Another pitfall others have had in the past is they have the add-in in
> several different folders and updated the files in the wrong folder -- that
> is, not the one pointed to by EXCEL's add-in manager. To double-check where
> your add-in is located:*
>
>
> *=RCHGetElementNumber("Version")*
>
>
>
> *In addition to telling you which version of the add-in is running, it
> will also tell you where it is located.*
>
>> The formula I am testing with () still shows error.
>>
>
>
>
>
>
> *Yes. It would with that definition. You can also try the new formula that
> is saved as the element
> definition:=RCHGetTableCell("http://research.investors.com/stock-checkup/xxxx-MMM.aspx
> <http://research.investors.com/stock-checkup/xxxx-MMM.aspx>",1,">Debt/Equity
> Ratio")*
>
>
>
>> It seemed from other posts that I would need have a login at
>> investors.com. I got partway in and they require me to give a street
>> address so they can send me a paper for some weeks. I don't like that and
>> much prefer not to do that; in fact, not even sure it is permitted at my
>> current address.
>>
>
>
>
>
> *The IBD data is proprietary and only available to website subscribers. I
> haven't received the newspaper for years. Didn't even know it was required
> to get a sample?*
>
> *I don't know if there's any way around it. I currently have an annual
> subscription with them, plus a monthly subscription for their e-Tables.*
>
>
>
>> So, bottom line, what, if anything, is required of me in that formula
>> above?
>>
>
>
> *See above. :)*
>
>
>

--
Gordon Pedersen

Fri Jan 8, 2016 8:25 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

On Fri, Jan 8, 2016 at 9:01 PM, Gordon Pedersen gordon@...wrote:

>
> I enabled and ran the recalc macro and now the definition shows:
>
> IBD;Debt/Equity Ratio;=RCHGetTableCell("
> http://research.investors.com/stock-checkup/xxxx-~~~~~.aspx",1,">Debt/Equity
> Ratio")
>

*That is the correct definition. But you shouldn't have needed to enable
anything though? Just the act of using the add-in manager to activate the
add-in means that all processing within the add-in is already authorized --
both macros and functions. No other security measures need to be taken to
allow the macros to run. So don't give other macros the ability to run
without your express permission.*

> I understand the web and programming well enough; not so used to Excel. I
> just need to know what exactly to change and where precisely to change it.
>
> File "9" has lots of "investors.com" lines. Many (all??) have the "
> xxxx-~~~~~" I see above.
>

*Yes. You shouldn't need to change anything if you have the correct files
in place.*

> Do all those lines need to change? Is there a quick way to do it you've
> already created? Otherwise, I can use my text editor.
>

*The fact you see them that way means they have already been changed.*

> Is the "xxxx" my ibd email address?
>

*That used to be the exchange and company name. The old formula looked them
up to place them into the formula. It was failing because IBD changed their
quotes page and extracting them required a change. I discovered the
exchange and company didn't NEED to be part of the URL, as long as there
was something there. So the "xxxx" is just a dummy value to take their
place. I think it could be almost anything.*

*It made the formula a lot simpler.*

> Is the "~~~~~" my ibd password?
>

*The "saved formula" for each definition uses the "~~~~~" as a placeholder
for what gets passed as the ticker symbol in the RCHGetElementNumber()
function.*

*Take a look at the smf-elements-info.txt file in the SMF folder. It has a
little write-up about how element definitions are constructed and used.*

> What else needs to be done? (I'm probably missing something....)
>

*Other then being able to log in, you should be good to go. But, like I
said, the data is proprietary to IBD and does require you to be a member
and logged in (using the EXCEL Web Query dialog or IE to create the IE
security cookie) to access the data.*

Fri Jan 8, 2016 8:53 pm (PST) . Posted by:

"Gordon Pedersen"

What I'd forgotten (it's been a trying week...) even though I read about it
yesterday was the Excel Web Query. Thanks for reminder.

When I followed the howtogeek.com instructions, Excel 2013 (or investors.com)
gave me innumerable errors and finally forced Excel to its knees before I
could get logged in. I restarted it.

Second time around, I cooperated better, or it liked me better. I got
logged in at https://investors.com, didn't do much, then pressed "Import"
button at bottom of E. Web Query window, which then closed. I ended up
disabling all refresh for this Data Connection.

I restarted excel again but the test formula still shows "Error".
(Formula, as reminder, reads: =RCHGetElementNumber($A2,2077) and $A2 is
"JNJ")

Maybe you could link me to the full instructions for doing this right,
since it feels like I didn't accomplish all I was supposed to.

On Fri, Jan 8, 2016 at 8:25 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> On Fri, Jan 8, 2016 at 9:01 PM, Gordon Pedersen gordon@...wrote:
>
>>
>> I enabled and ran the recalc macro and now the definition shows:
>>
>> IBD;Debt/Equity Ratio;=RCHGetTableCell("
>> http://research.investors.com/stock-checkup/xxxx-~~~~~.aspx",1,">Debt/Equity
>> Ratio")
>>
>
>
> *That is the correct definition. But you shouldn't have needed to enable
> anything though? Just the act of using the add-in manager to activate the
> add-in means that all processing within the add-in is already authorized --
> both macros and functions. No other security measures need to be taken to
> allow the macros to run. So don't give other macros the ability to run
> without your express permission.*
>
>
>
>> I understand the web and programming well enough; not so used to Excel.
>> I just need to know what exactly to change and where precisely to change it.
>>
>> File "9" has lots of "investors.com" lines. Many (all??) have the "
>> xxxx-~~~~~" I see above.
>>
>
>
> *Yes. You shouldn't need to change anything if you have the correct files
> in place.*
>
>
>
>
>> Do all those lines need to change? Is there a quick way to do it you've
>> already created? Otherwise, I can use my text editor.
>>
>
> *The fact you see them that way means they have already been changed.*
>
>
>
>> Is the "xxxx" my ibd email address?
>>
>
>
>
> *That used to be the exchange and company name. The old formula looked
> them up to place them into the formula. It was failing because IBD changed
> their quotes page and extracting them required a change. I discovered the
> exchange and company didn't NEED to be part of the URL, as long as there
> was something there. So the "xxxx" is just a dummy value to take their
> place. I think it could be almost anything.*
>
> *It made the formula a lot simpler.*
>
>
>
>> Is the "~~~~~" my ibd password?
>>
>
>
>
> *The "saved formula" for each definition uses the "~~~~~" as a placeholder
> for what gets passed as the ticker symbol in the RCHGetElementNumber()
> function.*
>
> *Take a look at the smf-elements-info.txt file in the SMF folder. It has a
> little write-up about how element definitions are constructed and used.*
>
>
>
>> What else needs to be done? (I'm probably missing something....)
>>
>
>
> *Other then being able to log in, you should be good to go. But, like I
> said, the data is proprietary to IBD and does require you to be a member
> and logged in (using the EXCEL Web Query dialog or IE to create the IE
> security cookie) to access the data.*
>
>
>
>

--
Gordon Pedersen

Fri Jan 8, 2016 10:14 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Take a look at this message:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/29425

Hopefully, something there will help.

As far as the EXCEL Web Query dialog (aka "IE object"), once you log in,
you could have just cancelled out. You didn't actually need to import
anything. Just create the IE security cookie.

On Fri, Jan 8, 2016 at 9:53 PM, Gordon Pedersen gordon@...wrote:

>
> What I'd forgotten (it's been a trying week...) even though I read about
> it yesterday was the Excel Web Query. Thanks for reminder.
>
> When I followed the howtogeek.com instructions, Excel 2013 (or
> investors.com) gave me innumerable errors and finally forced Excel to its
> knees before I could get logged in. I restarted it.
>
> Second time around, I cooperated better, or it liked me better. I got
> logged in at https://investors.com, didn't do much, then pressed "Import"
> button at bottom of E. Web Query window, which then closed. I ended up
> disabling all refresh for this Data Connection.
>
> I restarted excel again but the test formula still shows "Error".
> (Formula, as reminder, reads: =RCHGetElementNumber($A2,2077) and $A2 is
> "JNJ")
>
> Maybe you could link me to the full instructions for doing this right,
> since it feels like I didn't accomplish all I was supposed to.
>
>

Fri Jan 8, 2016 10:20 pm (PST) . Posted by:

truebangalorean

Hi there!


I have many excel files that use SMF functions and I am trying to move these excel files to my new PC. I placed the SMF Addin XLA file in the same location (path) as the previous PC where the files were developed. Despite that when the files are moved over, the functions use the full path to XLA file and even if that path is valid (to the addin file) in the new PC, the functions don't work.


Is there a better way to move the excel files to move the excel files across PCs so that the SMF functions will continue to work? (referencing the new addin file?)


Thanks for any advice!


Shri

Fri Jan 8, 2016 10:58 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Putting the add-in in the same location should have prevented that, unless
you didn't activate the add-in in the add-in manager from within EXCEL
first?

On Fri, Jan 8, 2016 at 11:20 PM, truebangalorean@... wrote:

>
> I have many excel files that use SMF functions and I am trying to move
> these excel files to my new PC. I placed the SMF Addin XLA file in the same
> location (path) as the previous PC where the files were developed. Despite
> that when the files are moved over, the functions use the full path to XLA
> file and even if that path is valid (to the addin file) in the new PC, the
> functions don't work.
>
> Is there a better way to move the excel files to move the excel files
> across PCs so that the SMF functions will continue to work? (referencing
> the new addin file?)
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar