Selasa, 08 April 2014

[smf_addin] Digest Number 3037

15 Messages

Digest #3037
1a
Anyone have trouble pulling Yahoo options quotes? by "Muhamand Wilkes" 506a0b14356aab29d46fcd963b5d7ce2
3b
3c
3e
4b
Re: Difficulty downloading SMF Add-in by "Randy Harmelink" rharmelink
4d
Re: Difficulty downloading SMF Add-in by "Randy Harmelink" rharmelink
5b
Re: Why Don't Any Files Work? by "Randy Harmelink" rharmelink

Messages

Tue Apr 8, 2014 2:59 am (PDT) . Posted by:

"Muhamand Wilkes" 506a0b14356aab29d46fcd963b5d7ce2

I can no longer pull any options quotes from Yahoo for the past couples of
days, anyone else having issues?

Tue Apr 8, 2014 7:53 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Their coverage has been spotty lately. For a few weeks, they didn't have
any options for SPY. I think that applies to other equities as well.
However, I just tried this and it worked fine:

=smfGetOptionQuotes("MMM 4/25 2014 $134 Call","b")

Last week, it was noted that Google also was missing option chains for some
equities.

On Tue, Apr 8, 2014 at 2:59 AM, Muhamand Wilkes
<muhamand.wilkes@gmail.com>wrote:

>
> I can no longer pull any options quotes from Yahoo for the past couples of
> days, anyone else having issues?
>

Tue Apr 8, 2014 7:13 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

Yes and don't forget to click save. Can't tell you how many times I have forgotten to click save and lost my changes.

Excel will ask when you close to save but I prefer to save it right after the link update.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of scottvo001@yahoo.com
Sent: Tuesday, April 08, 2014 2:14 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] How do i extract 10-year financial data for my companies? [1 Attachment]

[Attachment(s) from scottvo001@yahoo.com included below]

Linh,

Download RCH_Stock_Market_Functions, then follow these steps

Click Enable Content --> Edit Links --> Change Source --> Select the RCH_Stock_Market_Functions file --> OK --> Close

You should good to go.
Scott

Tue Apr 8, 2014 10:14 am (PDT) . Posted by:

colinmmoors

Hello,
why would 2 different computers get different results using RCHGetTableCell?
specifically, one returns an error and one gets the desired result for:
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=ETFC",1,"Shares Short (",,,,,,,0,)
I have tried various remedies on problem PC:
* logging in to Yahoo via excel
* clearing temp folder
* opening closing IE; clearing history/cache
* restarting
* copying latest smf_addin files
any other suggestions?
Thanks,
sea

Tue Apr 8, 2014 10:34 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

I'm guessing you do not have the link to the SMF Addin correct in the failing machine.

When you save the Excel file it adds the full link to the formula.

So if you close the file on one machine and open it on another you have the fix the links to point to the where the SMF addin is on that machine.

I run into that all the time as I use 5 different computers and store all my files on a common machine. So I have gooten to naming the files based on the machine I use it on the most.

It is a "Pain in the ass" but that's Microsoft Excel.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of colinmmoors@yahoo.com
Sent: Tuesday, April 08, 2014 1:15 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] 2 pc's get 2 different results RCHGetTableCell


Hello,
why would 2 different computers get different results using RCHGetTableCell?
specifically, one returns an error and one gets the desired result for:
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=ETFC",1,"Shares Short (",,,,,,,0,)
I have tried various remedies on problem PC:
* logging in to Yahoo via excel
* clearing temp folder
* opening closing IE; clearing history/cache
* restarting
* copying latest smf_addin files
any other suggestions?
Thanks,
sea

Tue Apr 8, 2014 10:39 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

There should be no need to log into Yahoo to get to that web page.

Do you get the error when entering the formula in a new workbook, or in a
workbook you are opening? If the latter, you may have unresolved links that
need to be fixed. So you would see an old value and it wouldn't update. But
you should get an error message from EXCEL on unresolved links.

Otherwise, I would have suggested the possibility of a firewall that
prevents access to the web page. But if you can get to Yahoo via EXCEL,
that shouldn't be an issue.

On Tue, Apr 8, 2014 at 10:14 AM, <colinmmoors@yahoo.com> wrote:

>
> why would 2 different computers get different results using
> RCHGetTableCell?
>
> specifically, one returns an error and one gets the desired result for:
>
> RCHGetTableCell("http://finance.yahoo.com/q/ks?s=ETFC",1,"Shares Short
> (",,,,,,,0,)
>
> I have tried various remedies on problem PC:
>
> * logging in to Yahoo via excel
>
> * clearing temp folder
>
> * opening closing IE; clearing history/cache
>
> * restarting
>
> * copying latest smf_addin files
>
> any other suggestions?
>
>

Tue Apr 8, 2014 12:17 pm (PDT) . Posted by:

colinmmoors

the problem seems to be specific to the workbook; if I open a new workbook on the same problem PC and paste the same formula, it works. In fact, the problem workbook does work intermittently. it is a huge spreadsheet which I have to recalc manually. strange that it is an intermittent problem - once it returns an error for a symbol, it constantly returns an error - until I re-open the workbook. strange. I'd rather not have to rebuild the workbook

Tue Apr 8, 2014 12:41 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Once it returns error for a particular web page extraction, it should
always return error -- until you exit the workbook or run the
smfForceRecalculation macro. That's because data is extracted from a SAVED
copy of the web page, and those are the only two ways to purge the saved
copy of the web page and get a fresh copy.

Your "manual recalc" should be done with the smfForceRecalculation macro.
Hitting F9 will NOT get a fresh copy of the web page for an
RCHGetTableCell() function. It just re-extracts the data from the saved
copy of the web page (which might be the very cause of the error).

You're sure EXCEL is not telling you about link errors when you open the
workbook?

On Tue, Apr 8, 2014 at 12:17 PM, <colinmmoors@yahoo.com> wrote:

>
> the problem seems to be specific to the workbook; if I open a new workbook
> on the same problem PC and paste the same formula, it works. In fact, the
> problem workbook does work intermittently. it is a huge spreadsheet which
> I have to recalc manually. strange that it is an intermittent problem -
> once it returns an error for a symbol, it constantly returns an error -
> until I re-open the workbook. strange. I'd rather not have to rebuild the
> workbook
>

Tue Apr 8, 2014 12:59 pm (PDT) . Posted by:

"Randy Randall" randyr_cds

colinmmoors@yahoo.com wrote: "it is a huge spreadsheet which I have to
recalc manually."

Randy H: Could this be symptomatic of exceeding the number of web pages?

Randy R

On Tue, Apr 8, 2014 at 3:17 PM, <colinmmoors@yahoo.com> wrote:

>
>
> the problem seems to be specific to the workbook; if I open a new workbook
> on the same problem PC and paste the same formula, it works. In fact, the
> problem workbook does work intermittently. it is a huge spreadsheet which
> I have to recalc manually. strange that it is an intermittent problem -
> once it returns an error for a symbol, it constantly returns an error -
> until I re-open the workbook. strange. I'd rather not have to rebuild the
> workbook
>
>

Tue Apr 8, 2014 10:36 am (PDT) . Posted by:

gaeisen

Randy, I get it. This leads to further questions, though:

1) I will be modifying this workbook, and sending it back to the friend who originated it. Since my cells are hard coded to reference C:\SMF Add-in and his aren't, will he need to run that macro in order to get the spreadsheet to work for him (since his copy of SMF is located under program files)?


2) THIS is the macro instruction, right?
Public Sub smfFixLinks()
ActiveSheet.Cells.Replace _
What:="'C:&#92;Program Files\SMF Add-In\RCH_Stock_Market_Functions.xla'!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub



3) My friend is not that computer literate. Is there any way I can get the Add-in to go into my C:\Program Files\SMF Add-in directory location? That would make it easier for my friend, since running a macro (or the alternative "Find and Replace All" function might be asking too much of him. Again, when I tried to do so, Win7 would not cooperate. I recognize what you said above about WIN7 being finicky. I don't know what operating system my friend is using.
I guess I could have my friend move his add-in to the preferred directory location!



Thank you.
Greg

Tue Apr 8, 2014 10:57 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Tue, Apr 8, 2014 at 10:36 AM, <g.eisen@comcast.net> wrote:

>
> Randy, I get it. This leads to further questions, though:
>
> 1) I will be modifying this workbook, and sending it back to the friend
> who originated it. Since my cells are hard coded to reference *C:\SMF
> Add-in *and his aren't, will he need to run that macro in order to get
> the spreadsheet to work for him (since his copy of SMF is located under
> program files)?
>

Correct. Because EXCEL saves the hard-coded location of the add-in whenever
a workbook is saved.

2) THIS is the macro instruction, right?
> Public Sub smfFixLinks()
> ActiveSheet.Cells.Replace _
> What:="'C:&#92;Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!", _
> Replacement:="", _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
> End Sub
>

Actually, that's an older version of the macro. I changed it to be more
universal, by using a wild card character for everything prior to the XLA
name. It also does all sheets in the workbook. Current version is:

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

> 3) My friend is not that computer literate. Is there any way I can get
> the Add-in to go into my *C:\Program Files\SMF Add-in* directory
> location? That would make it easier for my friend, since running a macro
> (or the alternative "Find and Replace All" function might be asking too
> much of him. Again, when I tried to do so, Win7 would not cooperate. I
> recognize what you said above about WIN7 being finicky. I don't know what
> operating system my friend is using.
> I guess I could have my friend move his add-in to the preferred directory
> location!
>

I gave up on trying to force it to allow me updating within the Program
Files folder under Vista. The macro was an easier thing for me to do.

If you do decide to change the location, I've found this to be the best way
to do it:

1. Go to the add-in manager and uncheck the add-in (i.e. current location)
2. Exit EXCEL (to purge any connection to the old location)
3. Restart EXCEL
4. Go to the add-in manager and put the add-in back into the list, using
the new location
5. Exit EXCEL (to save the new settings)
6. Restart EXCEL

Another option is to create a VBA event in the workbook that executes the
smfFixLinks macro whenever the workbook is opened. I've never done that,
but it should work.

Tue Apr 8, 2014 11:22 am (PDT) . Posted by:

codyklein

Not sure if this helps but I just put a quick video together on eliminating the hard coded location when downloading files. I believe the sheets must be unprotected to make changes.
https://www.youtube.com/watch?v=hSv-HLA1Rqo https://www.youtube.com/watch?v=hSv-HLA1Rqo

Tue Apr 8, 2014 11:40 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Very useful and instructive video. Thanks.

And, yes, the sheets must be unprotected in order to make those changes.

On Tue, Apr 8, 2014 at 11:22 AM, <codyklein@alum.drexel.edu> wrote:

>
> Not sure if this helps but I just put a quick video together on
> eliminating the hard coded location when downloading files. I believe the
> sheets must be unprotected to make changes.
> https://www.youtube.com/watch?v=hSv-HLA1Rqo
>

Tue Apr 8, 2014 12:17 pm (PDT) . Posted by:

almondtree

When you say have it assigned to toolbar button how do we do that? (excel 2010)

Tue Apr 8, 2014 12:36 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't use EXCEL 2010, but this should help:

http://www.dummies.com/how-to/content/how-to-add-an-excel-2010-macro-to-the-quick-access.html

On Tue, Apr 8, 2014 at 12:17 PM, <almondtree@yahoo.com> wrote:

>
> When you say have it assigned to toolbar button how do we do that? (excel
> 2010)
>
Diposting oleh EXCEL FEED LEARNING di 12.59

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)