15 Messages
Digest #3285
2b
Re: Incorrect data when using RCHGetYahooQuotes function with VBA by "Randy Harmelink" rharmelink
3b
Re: RCHGetYahooQuotes function giving incorrect results when called by "Randy Harmelink" rharmelink
6b
Re: unable to copy and paste RCHGetYahooQuotes with relative referen by "Randy Harmelink" rharmelink
Messages
Sat Jan 3, 2015 9:33 am (PST) . Posted by:
blm3@sbcglobal.net
Ok, Thanks, Randy. I knew it had to be something simple
Sat Jan 3, 2015 10:51 am (PST) . Posted by:
liwei.zhou
When using the RCHGetYahooQuotes function through excel VBA macro programming language, I am getting wildly incorrect results for some measures such as P/B and dividend yield among others. I have a user form that is displayed when the user clicks a button. The user selects from a listbox control on the form all the data items that he or she is interested in. The listbox is populated by looping through cells on another sheet in the same workbook. When the submit button is clicked, the results obtained are wildly inaccurate such as a PEG ratio in the hundreds or a dividend yield in the thousands(000's).
I am using Excel 2010.
Symbols I tested are DUK and WAG The current VBA code I am using to call this RCHGetYahooQuotes function:
outputcells.select
selection.FormulaArray = RCHGetYahooQuotes(tickerItems , dataItems,,,1)
outputcells is a range variable with columns = # of data items and rows = # of companies I am pulling.
tickerItems is a variable of type Range corresponding to the individual companies.
dataItems is a variable of type Range corresponding to the individual data items (P/B EPS, etc)
I appreciate any help the community may provide! Thanks in advance! :-D
I am using Excel 2010.
Symbols I tested are DUK and WAG The current VBA code I am using to call this RCHGetYahooQuotes function:
outputcells.
selection.FormulaAr
outputcells is a range variable with columns = # of data items and rows = # of companies I am pulling.
tickerItems is a variable of type Range corresponding to the individual companies.
dataItems is a variable of type Range corresponding to the individual data items (P/B EPS, etc)
I appreciate any help the community may provide! Thanks in advance! :-D
Sat Jan 3, 2015 11:44 am (PST) . Posted by:
"Randy Harmelink" rharmelink
PS: Walgreen's is now WBA. There is no data on Yahoo for WAG.
On Sat, Jan 3, 2015 at 10:27 AM, liwei.zhou@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> - Symbols I tested are DUK and WAG
>
>
On Sat, Jan 3, 2015 at 10:27 AM, liwei.zhou@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> - Symbols I tested are DUK and WAG
>
>
Sat Jan 3, 2015 10:51 am (PST) . Posted by:
liwei.zhou
I have a listbox on an Excel VBA userform containing individual items such as P/B , P/E etc. The listbox is populated from a range on sheet 2 of the same workbook using a VBA for loop. The user selects the data items he or she is interested in analyzing from the listbox on the form. Upon clicking a button, "submit" on the user form, the RCHGetYahooQuotes is called to return the data from Yahoo. Partial code I am using currently follows:
Set outputCells = Range("B3" , Range("B3").Offset(tickerCount , dataCount))
outputCells.select
selection.FormulaArray = RCHGetYahooQuotes(tickerItems , dataItems,,,1)
OutputCells is a variable of type Range with # columns = dataCount which equals the number of data items being called. The # rows = tickerCount which equals the number of companies been returned.
tickerItems is a variable of type range which has the individual companies on the spreadsheet. dataItems is a variable of type range which has the individual data items (P/B , P/E etc...) The items in the
named range dataItems is equal to the selected items in the listbox on the user form.
Right now, when selection.FormulaArray = RCHGetYahooQuotes(tickerItems , dataItems,,,1) being called, I am getting wildly incorrect values for some elements such as a PEG ratio in the hundreds. Other items such as EPS and P/B seems to be correct.
I am using Excel 2010 on a Windows 7 operating system.
Any help is appreciated! Thanks in advance!
Set outputCells = Range("
outputCells.
selection.FormulaAr
OutputCells is a variable of type Range with # columns = dataCount which equals the number of data items being called. The # rows = tickerCount which equals the number of companies been returned.
tickerItems is a variable of type range which has the individual companies on the spreadsheet. dataItems is a variable of type range which has the individual data items (P/B , P/E etc...) The items in the
named range dataItems is equal to the selected items in the listbox on the user form.
Right now, when selection.FormulaAr
I am using Excel 2010 on a Windows 7 operating system.
Any help is appreciated! Thanks in advance!
Sat Jan 3, 2015 11:38 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I think you're making it more difficult than it needs to be. It can be as
simple as something like:
Set rTickers = Range("A3:A10")
Set rItems = Range("B1:K1")
nTickers = rTickers.Rows.Count
nItems = rItems.Columns.Count
Range("B2").Resize(nTickers + 1, nItems + 1) =
RCHGetYahooQuotes(rTickers, rItems, , , 1, pDim1:=nTickers + 1,
pDim2:=nItems + 1)
I'm not sure why you would be doing any looping, nor why you would want to
retrieve the data one item at a time, when each retrieval requires an
Internet access and more time for it to complete?
I tried various items, including PEG ratio and dividend, and they came back
fine.
On Sat, Jan 3, 2015 at 11:33 AM, liwei.zhou@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have a listbox on an Excel VBA userform containing individual items such
> as P/B , P/E etc. The listbox is populated from a range on sheet 2 of the
> same workbook using a VBA for loop. The user selects the data items he or
> she is interested in analyzing from the listbox on the form. Upon clicking
> a button, "submit" on the user form, the RCHGetYahooQuotes is called to
> return the data from Yahoo. Partial code I am using currently follows:
>
> Set outputCells = Range("B3" , Range("B3").Offset(tickerCount ,
> dataCount))
> outputCells.select
> selection.FormulaArray = RCHGetYahooQuotes(tickerItems , dataItems,,,1)
>
> OutputCells is a variable of type Range with # columns = dataCount which
> equals the number of data items being called. The # rows = tickerCount
> which equals the number of companies been returned.
> tickerItems is a variable of type range which has the individual companies
> on the spreadsheet. dataItems is a variable of type range which has the
> individual data items (P/B , P/E etc...) The items in the
> named range dataItems is equal to the selected items in the listbox on
> the user form.
>
> Right now, when selection.FormulaArray = RCHGetYahooQuotes(tickerItems ,
> dataItems,,,1) being called, I am getting wildly incorrect values for
> some elements such as a PEG ratio in the hundreds. Other items such as EPS
> and P/B seems to be correct.
>
> I am using Excel 2010 on a Windows 7 operating system.
> Any help is appreciated! Thanks in advance!
>
>
simple as something like:
Set rTickers = Range("A3:A10")
Set rItems = Range("B1:K1")
nTickers = rTickers.Rows.Count
nItems = rItems.Columns.Count
Range("B2").Resize(nTickers + 1, nItems + 1) =
RCHGetYahooQuotes(rTickers, rItems, , , 1, pDim1:=nTickers + 1,
pDim2:=nItems + 1)
I'm not sure why you would be doing any looping, nor why you would want to
retrieve the data one item at a time, when each retrieval requires an
Internet access and more time for it to complete?
I tried various items, including PEG ratio and dividend, and they came back
fine.
On Sat, Jan 3, 2015 at 11:33 AM, liwei.zhou@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have a listbox on an Excel VBA userform containing individual items such
> as P/B , P/E etc. The listbox is populated from a range on sheet 2 of the
> same workbook using a VBA for loop. The user selects the data items he or
> she is interested in analyzing from the listbox on the form. Upon clicking
> a button, "submit" on the user form, the RCHGetYahooQuotes is called to
> return the data from Yahoo. Partial code I am using currently follows:
>
> Set outputCells = Range("
> dataCount))
> outputCells.
> selection.FormulaAr
>
> OutputCells is a variable of type Range with # columns = dataCount which
> equals the number of data items being called. The # rows = tickerCount
> which equals the number of companies been returned.
> tickerItems is a variable of type range which has the individual companies
> on the spreadsheet. dataItems is a variable of type range which has the
> individual data items (P/B , P/E etc...) The items in the
> named range dataItems is equal to the selected items in the listbox on
> the user form.
>
> Right now, when selection.FormulaAr
> dataItems,,,
> some elements such as a PEG ratio in the hundreds. Other items such as EPS
> and P/B seems to be correct.
>
> I am using Excel 2010 on a Windows 7 operating system.
> Any help is appreciated! Thanks in advance!
>
>
Sat Jan 3, 2015 2:01 pm (PST) . Posted by:
analyst101010
Thanks Randy,
I confess, you're a bit over my head now.
For clarity - I open Excel, and i immediately get an error message =
"C:/Program Files/SMF Add-In/RCH_Stock_Market_Functions.xla" could not be found. Check the spelling of the file name, and verify that the file location is correct"
I dismiss this, and then open a specific template .xls file I work with. These will be inactive, and the formulas will be written as
='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(x,y)'
...where previously they were just "=RCHGetElementNumber(X,Y)
I will then go into Excel options, uncheck the reference to the older add-in location (which shows up as "Rch_stock_market_functions" in the options), and direct it to the C:/SMF directory location.
The checked option then appears as "Stock Market Function Add-In". Don't know why the different appearance of the name, but there you go.
Then the template formulas all update themselves and i can go about working.
I was curious why Excel keeps looking for the 'old' location every time at startup?
I figured there was some deep-menu "Startup options" setting I could just change so that Excel never looks in the "program files" location anymore.
I'm not worried about any of the specific templates and their formulae - all are being constantly updated, so its not an issue of legacy incompatibility -> just the frustration of repeatedly having to tell Excel where to look for the add-in every time i reopen it
If you or anyone has a specific step-by-step fix, it would be much appreciated.
I looked for the "smfFixLinks" macro until my tools menu, and couldn't locate. Hope this helps. Again - i always appreciate any kind of support from everyone.
Thanks
I confess, you're a bit over my head now.
For clarity - I open Excel, and i immediately get an error message =
"C:/Program Files/SMF Add-In/RCH_Stock_
I dismiss this, and then open a specific template .xls file I work with. These will be inactive, and the formulas will be written as
='C:\
...where previously they were just "=RCHGetElemen
I will then go into Excel options, uncheck the reference to the older add-in location (which shows up as "Rch_stock_
The checked option then appears as "Stock Market Function Add-In"
Then the template formulas all update themselves and i can go about working.
I was curious why Excel keeps looking for the 'old' location every time at startup?
I figured there was some deep-menu "Startup options" setting I could just change so that Excel never looks in the "program files" location anymore.
I'm not worried about any of the specific templates and their formulae - all are being constantly updated, so its not an issue of legacy incompatibility -> just the frustration of repeatedly having to tell Excel where to look for the add-in every time i reopen it
If you or anyone has a specific step-by-step fix, it would be much appreciated.
I looked for the "smfFixLinks&q
Thanks
Sat Jan 3, 2015 3:50 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
On Sat, Jan 3, 2015 at 3:01 PM, jfgilmore@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I confess, you're a bit over my head now.
>
> For clarity - I open Excel, and i immediately get an error message =
>
> "C:/Program Files/SMF Add-In/RCH_Stock_Market_Functions.xla" could not be
> found. Check the spelling of the file name, and verify that the file
> location is correct"
>
> I dismiss this, and then open a specific template .xls file I work with.
> These will be inactive, and the formulas will be written as
>
> ='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(x,y)'
>
> ...where previously they were just "=RCHGetElementNumber(X,Y)
>
And that's what the smfFixLinks macro fixes.
All you need to do is a "Find and Replace All" at this point, changing, the
"'C:\SMF Add-In\RCH_Stock_Market_
Functions.xla'!" to be nothing. Then it will use the add-in's function just
fine.
That's all the smfFixLinks macro does, is automate that "Find and Replace
All" process.
> I will then go into Excel options, uncheck the reference to the older
> add-in location (which shows up as "Rch_stock_market_functions" in the
> options), and direct it to the C:/SMF directory location.
>
I haven't found that to be a reliable fix.
> The checked option then appears as "Stock Market Function Add-In". Don't
> know why the different appearance of the name, but there you go.
>
That sounds strange to me.
Then the template formulas all update themselves and i can go about
> working.
>
> I was curious why Excel keeps looking for the 'old' location every time at
> startup?
>
It's based on the location of the add-in when the individual WORKBOOK was
saved. It has NOTHING to do with your setup, but with the setup that was in
place when the workbook was saved. When the workbook is saved, there's
nothing in the saved file that indicates the workbook uses an add-in. Only
that it uses a function from another EXCEL file. That's why it hard-codes
the location of that file, so that when EXCEL opens the workbook later, it
knows where to find the file. When it doesn't find it there, it tags it as
an unresolved link -- which needs to be fixed. You can either do it
manually, or with the smfFixLinks macro.
I figured there was some deep-menu "Startup options" setting I could just
> change so that Excel never looks in the "program files" location anymore.
>
Nope. It's going to be looking at the location the workbook used at the
time it was saved. In general, that's only a problem when workbooks are
shared between different setups. Once you "fix" the links and save the
file, it will point to the location on your machine. So the next time you
open the file, there won't be a problem.
> I'm not worried about any of the specific templates and their formulae -
> all are being constantly updated, so its not an issue of legacy
> incompatibility -> just the frustration of repeatedly having to tell Excel
> where to look for the add-in every time i reopen it
>
You probably haven't saved the files after fixing the links?
I looked for the "smfFixLinks" macro until my tools menu, and couldn't
> locate.
>
In EXCEL 2007, there is no "tools" menu. But if you use the alt+F8 keyboard
shortcut to pull up the macro menu, you should be able to type the name of
the macro in and then run it. It isn't listed, because EXCEL only lists
macros from "All Open Workbooks" -- that doesn't include add-in files.
smf_addin@yahoogroups.com> wrote:
>
> I confess, you're a bit over my head now.
>
> For clarity - I open Excel, and i immediately get an error message =
>
> "C:/Program Files/SMF Add-In/RCH_Stock_
> found. Check the spelling of the file name, and verify that the file
> location is correct"
>
> I dismiss this, and then open a specific template .xls file I work with.
> These will be inactive, and the formulas will be written as
>
> ='C:\
>
> ...where previously they were just "=RCHGetElemen
>
And that's what the smfFixLinks macro fixes.
All you need to do is a "Find and Replace All" at this point, changing, the
"'C:
Functions.xla&#
fine.
That's all the smfFixLinks macro does, is automate that "Find and Replace
All" process.
> I will then go into Excel options, uncheck the reference to the older
> add-in location (which shows up as "Rch_stock_
> options), and direct it to the C:/SMF directory location.
>
I haven't found that to be a reliable fix.
> The checked option then appears as "Stock Market Function Add-In"
> know why the different appearance of the name, but there you go.
>
That sounds strange to me.
Then the template formulas all update themselves and i can go about
> working.
>
> I was curious why Excel keeps looking for the 'old' location every time at
> startup?
>
It's based on the location of the add-in when the individual WORKBOOK was
saved. It has NOTHING to do with your setup, but with the setup that was in
place when the workbook was saved. When the workbook is saved, there's
nothing in the saved file that indicates the workbook uses an add-in. Only
that it uses a function from another EXCEL file. That's why it hard-codes
the location of that file, so that when EXCEL opens the workbook later, it
knows where to find the file. When it doesn't find it there, it tags it as
an unresolved link -- which needs to be fixed. You can either do it
manually, or with the smfFixLinks macro.
I figured there was some deep-menu "Startup options" setting I could just
> change so that Excel never looks in the "program files" location anymore.
>
Nope. It's going to be looking at the location the workbook used at the
time it was saved. In general, that's only a problem when workbooks are
shared between different setups. Once you "fix" the links and save the
file, it will point to the location on your machine. So the next time you
open the file, there won't be a problem.
> I'm not worried about any of the specific templates and their formulae -
> all are being constantly updated, so its not an issue of legacy
> incompatibility -> just the frustration of repeatedly having to tell Excel
> where to look for the add-in every time i reopen it
>
You probably haven't saved the files after fixing the links?
I looked for the "smfFixLinks&q
> locate.
>
In EXCEL 2007, there is no "tools" menu. But if you use the alt+F8 keyboard
shortcut to pull up the macro menu, you should be able to type the name of
the macro in and then run it. It isn't listed, because EXCEL only lists
macros from "All Open Workbooks" -- that doesn't include add-in files.
Sat Jan 3, 2015 8:47 pm (PST) . Posted by:
analyst101010
I suspect the issue with my Excel startup is that the "Blank workbook" template is associated with the 'old' add in.
i.e. when i start excel, it *immediately* gives me the error - before i've opened anything.
you can see the startup message here
http://i.imgur.com/afH8r5S.jpg http://i.imgur.com/afH8r5S.jpg
http://i.imgur.com/afH8r5S.jpg
http://i.imgur.com/afH8r5S.jpg http://i.imgur.com/afH8r5S.jpg
View on i.imgur.com http://i.imgur.com/afH8r5S.jpg
Preview by Yahoo
The add-in that is "missing" (deleted from the Program Files directory) is still referenced in the blank-workbook as a default 'add-in'
you can see how it is listed here
http://i.imgur.com/jpDDlwB.jpg http://i.imgur.com/jpDDlwB.jpg
http://i.imgur.com/jpDDlwB.jpg
http://i.imgur.com/jpDDlwB.jpg http://i.imgur.com/jpDDlwB.jpg
View on i.imgur.com http://i.imgur.com/jpDDlwB.jpg
Preview by Yahoo
I then uncheck that, and redirect the page to the new add-in location, and it reads differently, as you can see below
http://i.imgur.com/rXEUgCK.jpg http://i.imgur.com/rXEUgCK.jpg
http://i.imgur.com/rXEUgCK.jpg
http://i.imgur.com/rXEUgCK.jpg http://i.imgur.com/rXEUgCK.jpg
View on i.imgur.com http://i.imgur.com/rXEUgCK.jpg
Preview by Yahoo
All of this happens before i've even opened an older workbook with any actual RCH formulae in them - just with a "blank" sheet on opening\
I tried checking to see if there was a file in the XLStart folder (in Programs/MS Office/Office12/XLStart) - which would automatically load on startup - which had been associated with the "old" add-in, but no, it was empty. I tried creating a new blank template associated with the new add-in location but the error persists.
This is why i'm wondering whether there's some startup setting menu i've missed/forgotten about. I have no idea why it would continue to reference the old add-in location when no workbooks have any formula associated with it, or any files exist in that location.
sorry to belabor this, but maybe i wasn't clear - this error where it continues to look for the 'old' add-in location persists despite there being no actual workbook files open, or even recently open, that refer to it. It seems to want to look for that Add-in on startup.
Thanks
i.e. when i start excel, it *immediately* gives me the error - before i've opened anything.
you can see the startup message here
http://i.imgur.com/afH8r5S.jpg http://i.imgur.com/afH8r5S.jpg
http://i.imgur.com/afH8r5S.jpg
http://i.imgur.com/afH8r5S.jpg http://i.imgur.com/afH8r5S.jpg
View on i.imgur.com http://i.imgur.com/afH8r5S.jpg
Preview by Yahoo
The add-in that is "missing" (deleted from the Program Files directory) is still referenced in the blank-workbook as a default 'add-in'
you can see how it is listed here
http://i.imgur.com/jpDDlwB.jpg http://i.imgur.com/jpDDlwB.jpg
http://i.imgur.com/jpDDlwB.jpg
http://i.imgur.com/jpDDlwB.jpg http://i.imgur.com/jpDDlwB.jpg
View on i.imgur.com http://i.imgur.com/jpDDlwB.jpg
Preview by Yahoo
I then uncheck that, and redirect the page to the new add-in location, and it reads differently, as you can see below
http://i.imgur.com/rXEUgCK.jpg http://i.imgur.com/rXEUgCK.jpg
http://i.imgur.com/rXEUgCK.jpg
http://i.imgur.com/rXEUgCK.jpg http://i.imgur.com/rXEUgCK.jpg
View on i.imgur.com http://i.imgur.com/rXEUgCK.jpg
Preview by Yahoo
All of this happens before i've even opened an older workbook with any actual RCH formulae in them - just with a "blank" sheet on opening\
I tried checking to see if there was a file in the XLStart folder (in Programs/MS Office/Office12/
This is why i'm wondering whether there's some startup setting menu i've missed/forgotten about. I have no idea why it would continue to reference the old add-in location when no workbooks have any formula associated with it, or any files exist in that location.
sorry to belabor this, but maybe i wasn't clear - this error where it continues to look for the 'old' add-in location persists despite there being no actual workbook files open, or even recently open, that refer to it. It seems to want to look for that Add-in on startup.
Thanks
Sat Jan 3, 2015 9:03 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Ahhhh. Now I understand your issue. I suspect you changed the location of
the add-in at one point, while EXCEL was open. That sometimes causes
issues. The add-in is loaded at start-up time, BEFORE your "blank template"
is loaded. From the picture you posted, it appears you're getting that
message at that point.
These steps should fix everything up:
1. Go to the add-in manager and uncheck the add-in (i.e. existing pointed
to 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
its correct location
5. Exit EXCEL (to save the new settings)
6. Restart EXCEL
Hopefully, from that point on, you won't get the start-up error message?
On Sat, Jan 3, 2015 at 9:47 PM, jfgilmore@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I suspect the issue with my Excel startup is that the "Blank workbook"
> template is associated with the 'old' add in.
>
> i.e. when i start excel, it *immediately* gives me the error - before i've
> opened anything.
>
> you can see the startup message here
>
> http://i.imgur.com/afH8r5S.jpg
> [image: image] <http://i.imgur.com/afH8r5S.jpg>
> http://i.imgur.com/afH8r5S.jpg
> View on i.imgur.com <http://i.imgur.com/afH8r5S.jpg>
> Preview by Yahoo
>
>
> The add-in that is "missing" (deleted from the Program Files directory) is
> still referenced in the blank-workbook as a default 'add-in'
>
> you can see how it is listed here
>
> http://i.imgur.com/jpDDlwB.jpg
>
>
>
> [image: image] <http://i.imgur.com/jpDDlwB.jpg>
> http://i.imgur.com/jpDDlwB.jpg
> View on i.imgur.com <http://i.imgur.com/jpDDlwB.jpg>
> Preview by Yahoo
>
>
>
> I then uncheck that, and redirect the page to the new add-in location, and
> it reads differently, as you can see below
>
> http://i.imgur.com/rXEUgCK.jpg
>
>
> [image: image] <http://i.imgur.com/rXEUgCK.jpg>
> http://i.imgur.com/rXEUgCK.jpg
> View on i.imgur.com <http://i.imgur.com/rXEUgCK.jpg>
> Preview by Yahoo
>
>
>
> All of this happens before i've even opened an older workbook with any
> actual RCH formulae in them - just with a "blank" sheet on opening\
>
> I tried checking to see if there was a file in the XLStart folder (in
> Programs/MS Office/Office12/XLStart) - which would automatically load on
> startup - which had been associated with the "old" add-in, but no, it was
> empty. I tried creating a new blank template associated with the new add-in
> location but the error persists.
>
> This is why i'm wondering whether there's some startup setting menu i've
> missed/forgotten about. I have no idea why it would continue to reference
> the old add-in location when no workbooks have any formula associated with
> it, or any files exist in that location.
>
> sorry to belabor this, but maybe i wasn't clear - this error where it
> continues to look for the 'old' add-in location persists despite there
> being no actual workbook files open, or even recently open, that refer to
> it. It seems to want to look for that Add-in on startup.
>
the add-in at one point, while EXCEL was open. That sometimes causes
issues. The add-in is loaded at start-up time, BEFORE your "blank template"
is loaded. From the picture you posted, it appears you're getting that
message at that point.
These steps should fix everything up:
1. Go to the add-in manager and uncheck the add-in (i.e. existing pointed
to 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
its correct location
5. Exit EXCEL (to save the new settings)
6. Restart EXCEL
Hopefully, from that point on, you won't get the start-up error message?
On Sat, Jan 3, 2015 at 9:47 PM, jfgilmore@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I suspect the issue with my Excel startup is that the "Blank workbook"
> template is associated with the 'old' add in.
>
> i.e. when i start excel, it *immediately* gives me the error - before i've
> opened anything.
>
> you can see the startup message here
>
> http://i.imgur.com/afH8r5S.jpg
> [image: image] <http://i.imgur.com/afH8r5S.jpg>
> http://i.imgur.com/afH8r5S.jpg
> View on i.imgur.com <http://i.imgur.com/afH8r5S.jpg>
> Preview by Yahoo
>
>
> The add-in that is "missing" (deleted from the Program Files directory) is
> still referenced in the blank-workbook as a default 'add-in'
>
> you can see how it is listed here
>
> http://i.imgur.com/jpDDlwB.jpg
>
>
>
> [image: image] <http://i.imgur.com/jpDDlwB.jpg>
> http://i.imgur.com/jpDDlwB.jpg
> View on i.imgur.com <http://i.imgur.com/jpDDlwB.jpg>
> Preview by Yahoo
>
>
>
> I then uncheck that, and redirect the page to the new add-in location, and
> it reads differently, as you can see below
>
> http://i.imgur.com/rXEUgCK.jpg
>
>
> [image: image] <http://i.imgur.com/rXEUgCK.jpg>
> http://i.imgur.com/rXEUgCK.jpg
> View on i.imgur.com <http://i.imgur.com/rXEUgCK.jpg>
> Preview by Yahoo
>
>
>
> All of this happens before i've even opened an older workbook with any
> actual RCH formulae in them - just with a "blank" sheet on opening\
>
> I tried checking to see if there was a file in the XLStart folder (in
> Programs/MS Office/Office12/
> startup - which had been associated with the "old" add-in, but no, it was
> empty. I tried creating a new blank template associated with the new add-in
> location but the error persists.
>
> This is why i'm wondering whether there's some startup setting menu i've
> missed/forgotten about. I have no idea why it would continue to reference
> the old add-in location when no workbooks have any formula associated with
> it, or any files exist in that location.
>
> sorry to belabor this, but maybe i wasn't clear - this error where it
> continues to look for the 'old' add-in location persists despite there
> being no actual workbook files open, or even recently open, that refer to
> it. It seems to want to look for that Add-in on startup.
>
Sat Jan 3, 2015 11:18 pm (PST) . Posted by:
analyst101010
LOL
yes, that did it.
Idiotic it was so simple. I think i was always over-thinking the problem. Thanks Randy.
yes, that did it.
Idiotic it was so simple. I think i was always over-thinking the problem. Thanks Randy.
Sat Jan 3, 2015 2:22 pm (PST) . Posted by:
analyst101010
Yes - just use the =SMFpricesbydates(ticker,date) formula
E.g.
- in column A (A2,A3,A4,A5, etc), list your tickers
- in Row 1, list your dates
I usually set B1 to "=TODAY()" - i.e., formatted in month/day/year,
- then C1 to "=B1-$A1", where A1 is the time period i want to measure
e.g. 7days, 30days, 90days, 365days...
and so forth.... D1 = C1-$A1 etc.
e.g. if your A1 value is '30 days', the dates in Row 1 would be "Today, Today-30 days, today-60 days, today-90 days, today-120 days, etc etc.
Then you use the SMFpricesbydates formula, referencing the ticker & date
e.g. in cell B2,"=SMFpricesbydates($A2,B1....)" with the date changing as you drag the formula across the row .
While i find this works for doing a 'monthly comp sheet' to compare large #s of stocks - 100+ - over a large range of dates ....i don't find it convenient for 'live, daily updating' sheets, as it will take ages to update all the individual fields.
I think if you have fewer tickers, and want to pull down lots of daily historical price data (e.g. daily data for 10years) see the "RCHGetYahooHistory" function, which i think would probably work better in that case. I use that in a different kind of template where I compare small #s of stocks (3-5 or so) against daily S&P data to show market correlation.
E.g.
- in column A (A2,A3,A4,A5, etc), list your tickers
- in Row 1, list your dates
I usually set B1 to "=TODAY(
- then C1 to "=B1-$A1"
e.g. 7days, 30days, 90days, 365days...
and so forth.... D1 = C1-$A1 etc.
e.g. if your A1 value is '30 days', the dates in Row 1 would be "Today, Today-30 days, today-60 days, today-90 days, today-120 days, etc etc.
Then you use the SMFpricesbydates formula, referencing the ticker & date
e.g. in cell B2,"=SMFprices
While i find this works for doing a 'monthly comp sheet' to compare large #s of stocks - 100+ - over a large range of dates ....i don't find it convenient for 'live, daily updating' sheets, as it will take ages to update all the individual fields.
I think if you have fewer tickers, and want to pull down lots of daily historical price data (e.g. daily data for 10years) see the "RCHGetYahooHi
Sat Jan 3, 2015 3:25 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Several points to consider:
1. smfPricesByDates() only returns closing prices that are adjusted for
dividends and splits. The primary intent is to provide data that can be
used to compute total returns between various dates.
2. Using the TODAY() function makes every function that uses it volatile.
That means if you do ANYTHING in the worksheet, those functions will
recalculate. If you have a number of such items, it can be a long
recalculation period. Instead, what I usually do is get the "current date"
with this formula, which gets the last date that SPY was traded on:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
3. Instead of using individual smfPricesByDates() for each cell, you can
array-enter it for a single ticker and multiple dates. If you are
retrieving prices for five different dates for a ticker symbol, that would
mean you only need one Internet access to get the data instead of five of
them. Note that you cannot specify a range of ticker symbols. So my formula
would end up being something like this (array-entered over B2:J2):
=smfPricesByDates(A2,B1:J1)
4. If you are going to create a table like the one you describe, be sure to
check out the smfPricesByDates templates on the add-in website, as they
already have things set up. You just need to change the tickers and dates,
as needed:
http://ogres-crypt.com/SMF/Templates/#smfPricesByDates%28%29
On Sat, Jan 3, 2015 at 3:22 PM, jfgilmore@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Yes - just use the =SMFpricesbydates(ticker,date) formula
>
> E.g.
>
> - in column A (A2,A3,A4,A5, etc), list your tickers
>
> - in Row 1, list your dates
>
> I usually set B1 to "=TODAY()" - i.e., formatted in month/day/year,
>
> - then C1 to "=B1-$A1", where A1 is the time period i want to measure
>
> e.g. 7days, 30days, 90days, 365days...
>
> and so forth.... D1 = C1-$A1 etc.
>
> e.g. if your A1 value is '30 days', the dates in Row 1 would be "Today,
> Today-30 days, today-60 days, today-90 days, today-120 days, etc etc.
>
> Then you use the SMFpricesbydates formula, referencing the ticker & date
>
> e.g. in cell B2,"=SMFpricesbydates($A2,B1....)" with the date changing as
> you drag the formula across the row .
>
> While i find this works for doing a 'monthly comp sheet' to compare large
> #s of stocks - 100+ - over a large range of dates ....i don't find it
> convenient for 'live, daily updating' sheets, as it will take ages to
> update all the individual fields.
>
> I think if you have fewer tickers, and want to pull down lots of daily
> historical price data (e.g. daily data for 10years) see the
> "RCHGetYahooHistory" function, which i think would probably work better in
> that case. I use that in a different kind of template where I compare small
> #s of stocks (3-5 or so) against daily S&P data to show market correlation.
>
1. smfPricesByDates() only returns closing prices that are adjusted for
dividends and splits. The primary intent is to provide data that can be
used to compute total returns between various dates.
2. Using the TODAY() function makes every function that uses it volatile.
That means if you do ANYTHING in the worksheet, those functions will
recalculate. If you have a number of such items, it can be a long
recalculation period. Instead, what I usually do is get the "current date"
with this formula, which gets the last date that SPY was traded on:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
3. Instead of using individual smfPricesByDates() for each cell, you can
array-enter it for a single ticker and multiple dates. If you are
retrieving prices for five different dates for a ticker symbol, that would
mean you only need one Internet access to get the data instead of five of
them. Note that you cannot specify a range of ticker symbols. So my formula
would end up being something like this (array-entered over B2:J2):
=smfPricesByDates(A2,B1:J1)
4. If you are going to create a table like the one you describe, be sure to
check out the smfPricesByDates templates on the add-in website, as they
already have things set up. You just need to change the tickers and dates,
as needed:
http://ogres-crypt.com/SMF/Templates/#smfPricesByDates%28%29
On Sat, Jan 3, 2015 at 3:22 PM, jfgilmore@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Yes - just use the =SMFpricesbydates(
>
> E.g.
>
> - in column A (A2,A3,A4,A5, etc), list your tickers
>
> - in Row 1, list your dates
>
> I usually set B1 to "=TODAY(
>
> - then C1 to "=B1-$A1"
>
> e.g. 7days, 30days, 90days, 365days...
>
> and so forth.... D1 = C1-$A1 etc.
>
> e.g. if your A1 value is '30 days', the dates in Row 1 would be "Today,
> Today-30 days, today-60 days, today-90 days, today-120 days, etc etc.
>
> Then you use the SMFpricesbydates formula, referencing the ticker & date
>
> e.g. in cell B2,"=SMFprices
> you drag the formula across the row .
>
> While i find this works for doing a 'monthly comp sheet' to compare large
> #s of stocks - 100+ - over a large range of dates ....i don't find it
> convenient for 'live, daily updating' sheets, as it will take ages to
> update all the individual fields.
>
> I think if you have fewer tickers, and want to pull down lots of daily
> historical price data (e.g. daily data for 10years) see the
> "RCHGetYahooHi
> that case. I use that in a different kind of template where I compare small
> #s of stocks (3-5 or so) against daily S&P data to show market correlation.
>
Sat Jan 3, 2015 3:10 pm (PST) . Posted by:
rydabbieri
I've been using SMF add-in for many years (Thank you Randy!!!) and with several variants of excel, but this is the first time I've come across this problem, which started a day or two ago.
I'm starting with a cell that returns the proper value:
A12 has the value of MSFT
A8=RCHGetYahooQuotes(A12,"l1")
and returns the proper value.
B12 has the value of MRK
When I copy cell A8 and paste it to B8, it gets replaced with =RCHGetYahooQuotes(#REF!,"l1")
It only works if I put in anchors or the actual ticker (i.e. =RCHGetYahooQuotes($B$12,"l1" or =RCHGetYahooQuotes("MRK","l1"))
I'm using Office 2007 and just recently replaced a version of SMF add-in that I've been using for years with
I had no problems the first few days after the upgrade, but I have no idea what's going on now.
Anyone else have this issue?
Thanks,
Ryan
I'm starting with a cell that returns the proper value:
A12 has the value of MSFT
A8=RCHGetYahooQuote
and returns the proper value.
B12 has the value of MRK
When I copy cell A8 and paste it to B8, it gets replaced with =RCHGetYahooQuotes(
It only works if I put in anchors or the actual ticker (i.e. =RCHGetYahooQuotes(
I'm using Office 2007 and just recently replaced a version of SMF add-in that I've been using for years with
I had no problems the first few days after the upgrade, but I have no idea what's going on now.
Anyone else have this issue?
Thanks,
Ryan
Sat Jan 3, 2015 3:34 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Can you create a small workbook with an example of that, and upload it?
Just do the copy to B8 so I can see the #REF! reference. What you're
describing shouldn't be happening.
But the change to #REF! is something that EXCEL would be doing.
What happens if cell A8 just has "=A12" and you move it to cell B8? Do you
get "=B12" or the #REF! error?
Does it happen only in an existing workbook, or in a brand new workbook as
well?
On Sat, Jan 3, 2015 at 1:57 PM, ryandabbieri@hotmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I've been using SMF add-in for many years (Thank you Randy!!!) and with
> several variants of excel, but this is the first time I've come across this
> problem, which started a day or two ago.
>
> I'm starting with a cell that returns the proper value:
>
> A12 has the value of MSFT
>
> A8=RCHGetYahooQuotes(A12,"l1")
>
> and returns the proper value.
>
> B12 has the value of MRK
>
> When I copy cell A8 and paste it to B8, it gets replaced with
> =RCHGetYahooQuotes(#REF!,"l1")
>
> It only works if I put in anchors or the actual ticker (i.e.
> =RCHGetYahooQuotes($B$12,"l1" or =RCHGetYahooQuotes("MRK","l1"))
>
> I'm using Office 2007 and just recently replaced a version of SMF add-in
> that I've been using for years with
>
> I had no problems the first few days after the upgrade, but I have no idea
> what's going on now.
>
> Anyone else have this issue?
>
Just do the copy to B8 so I can see the #REF! reference. What you're
describing shouldn't be happening.
But the change to #REF! is something that EXCEL would be doing.
What happens if cell A8 just has "=A12" and you move it to cell B8? Do you
get "=B12" or the #REF! error?
Does it happen only in an existing workbook, or in a brand new workbook as
well?
On Sat, Jan 3, 2015 at 1:57 PM, ryandabbieri@hotmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I've been using SMF add-in for many years (Thank you Randy!!!) and with
> several variants of excel, but this is the first time I've come across this
> problem, which started a day or two ago.
>
> I'm starting with a cell that returns the proper value:
>
> A12 has the value of MSFT
>
> A8=RCHGetYahooQuote
>
> and returns the proper value.
>
> B12 has the value of MRK
>
> When I copy cell A8 and paste it to B8, it gets replaced with
> =RCHGetYahooQuotes(
>
> It only works if I put in anchors or the actual ticker (i.e.
> =RCHGetYahooQuotes(
>
> I'm using Office 2007 and just recently replaced a version of SMF add-in
> that I've been using for years with
>
> I had no problems the first few days after the upgrade, but I have no idea
> what's going on now.
>
> Anyone else have this issue?
>
Sun Jan 4, 2015 12:09 am (PST) . Posted by:
analyst101010
Would there be some way to pull that same # for a group of stocks down? I've tried that before, by inserting an excel cell reference inside the quotes where the HTML address is, but obviously that doesn't work...
...im' thinking - could you write a second set of cells where the HTML address is created from a group of stock tickers by concatenating the result?
I'm just curious how that might work for say, a selected group of 10 or so 'comparison' tickers...
...im' thinking - could you write a second set of cells where the HTML address is created from a group of stock tickers by concatenating the result?
I'm just curious how that might work for say, a selected group of 10 or so 'comparison&#
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar