9 New Messages
Digest #2713
4b
Re: FAQ: What is the smfForceRecalculation macro and how do I use it by "Randy Harmelink" rharmelink
5b
Re: Problem reading ticker symbol for Indian stocks in Morningstar by "Randy Harmelink" rharmelink
5c
Re: Problem reading ticker symbol for Indian stocks in Morningstar by "Randy Harmelink" rharmelink
Messages
Sat Jul 27, 2013 2:02 am (PDT) . Posted by:
"oryxius" oryxius
Thank you both for the response. The removal of the MDI feature is indeed a major failing of MS. Why have they removed it? Is it because they are trying to move their base toward online use? Pete, if you do not mind me asking, what advantages does Office 2013 have over 2010?
Thank you,
m.k.
--- In smf_addin@yahoogroups.com , "PeteA" <optionzz@...> wrote:
>
> Yes, SMF works fine with 2013. I use it every day.
> BUT, 2013 does NOT support the MDI (Multiple Document Interface) a MAJOR,
> MAJOR failing for the way I use Excel, so I kept my 2010 license installed
> too for when I need several sheets open in the same window.
>
> Thanks,
> Pete A
> <http://www.sundognorth.com/TtP-S/ >
>
Thank you,
m.k.
--- In smf_addin@yahoogrou
>
> Yes, SMF works fine with 2013. I use it every day.
> BUT, 2013 does NOT support the MDI (Multiple Document Interface) a MAJOR,
> MAJOR failing for the way I use Excel, so I kept my 2010 license installed
> too for when I need several sheets open in the same window.
>
> Thanks,
> Pete A
> <http://www.sundogno
>
Sat Jul 27, 2013 6:55 am (PDT) . Posted by:
"quasiwasp" quasiwasp
"You say it does not work. What did you expect it to do, and what results did you see?"
On my computer, when i use Ctrl + Shift + R all stock prices in the workbook using "RCHGetYahooQuotes() update to the current prices. On other computers, nothing happens when I use the shortcut. I tried running the smfForceRecalculation manually, but nothing happened. When I clicked a particular cell containing the "RCHGetYahooQuotes() formula and hit enter on computers other than my own, the stock price updates.
As for the smfFixLinks, each time I close the sheet on my bosses computer, open the sheet on my computer the Fix Links Macro is the only thing that seems to correct the "NAME?" errors in the sheet,
If I've opened, saved and closed the sheet on my computer, then open it on my bosses computer, the add is no longer active on my bosses computer. It still shows up on the active Add in list. But in order to get it to work (any function invovled with it) I have to go back to the Excel Add In, Browse, and remap (if that's the right terminology) the add in to his excel.
On all of these computers, the add in Folder is located in C:\Program Files\SMF Add in.
There are 3 computers in use. These problems only arise when the add is used on 1 of the computers and then used on one of the other 2 computers. Computers 2 and 3 can both use the add in and the spreadsheet on Dropbox without any of these issues. I'm terribly confused by all of it...
Thanks so much again for the help.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> NEVER enable all macros. That just opens yourself up to all types of
> problems. And you don't need it for an add-in anyway, since adding the
> add-in already enabled it for any security issues. So do be careful about
> what add-ins you use.
>
> You shouldn't need to update Internet settings for smfForceRecalculation to
> work. All it does is empty out the saved web pages that the add-in has
> stored, and then trigger a normal EXCEL recalculation. You could achieve
> the same result by exiting EXCEL and restarting it, which resets the VBA
> environment.
>
> You say it does not work. What did you expect it to do, and what results
> did you see?
>
> And, BTW, you shouldn't need to use smfFixLinks every time. As long as the
> workbook is saved and opened with the add-in in the same location, it
> shouldn't be necessary. Typically, it only needs to be done once, because
> after fixing the links, you save the workbook on your own computer and
> reopen from there -- in which case the links should be automatically
> resolved.
>
> On Fri, Jul 26, 2013 at 1:24 PM, quasiwasp <joannapalmerjunk@...>wrote:
>
> > Thanks for the swift Repsonse!
> > The "smffixlinks" works perfectly, even we'll have to use it everytime.
> > This was caused by an additional drive on his computer that for some reason
> > references back to his C drive. So the excel file was trying to find:
> > X/:ProgramFiles/C:Program Files
> > Go figure.
> >
> > Another quick question -
> > I tried to use the smfForceRecalculation and it does not work on my
> > bosses. I updated the Internet settings per the "Links" section, but to no
> > avail. I also enabled all Macros.
> >
> > I have not been able to find a thread where changing the internet settins
> > did not work. Any thoughts?
> >
>
On my computer, when i use Ctrl + Shift + R all stock prices in the workbook using "RCHGetYahooQu
As for the smfFixLinks, each time I close the sheet on my bosses computer, open the sheet on my computer the Fix Links Macro is the only thing that seems to correct the "NAME?" errors in the sheet,
If I've opened, saved and closed the sheet on my computer, then open it on my bosses computer, the add is no longer active on my bosses computer. It still shows up on the active Add in list. But in order to get it to work (any function invovled with it) I have to go back to the Excel Add In, Browse, and remap (if that's the right terminology) the add in to his excel.
On all of these computers, the add in Folder is located in C:\Program Files\SMF Add in.
There are 3 computers in use. These problems only arise when the add is used on 1 of the computers and then used on one of the other 2 computers. Computers 2 and 3 can both use the add in and the spreadsheet on Dropbox without any of these issues. I'm terribly confused by all of it...
Thanks so much again for the help.
--- In smf_addin@yahoogrou
>
> NEVER enable all macros. That just opens yourself up to all types of
> problems. And you don't need it for an add-in anyway, since adding the
> add-in already enabled it for any security issues. So do be careful about
> what add-ins you use.
>
> You shouldn'
> work. All it does is empty out the saved web pages that the add-in has
> stored, and then trigger a normal EXCEL recalculation. You could achieve
> the same result by exiting EXCEL and restarting it, which resets the VBA
> environment.
>
> You say it does not work. What did you expect it to do, and what results
> did you see?
>
> And, BTW, you shouldn'
> workbook is saved and opened with the add-in in the same location, it
> shouldn'
> after fixing the links, you save the workbook on your own computer and
> reopen from there -- in which case the links should be automatically
> resolved.
>
> On Fri, Jul 26, 2013 at 1:24 PM, quasiwasp <joannapalmerjun
>
> > Thanks for the swift Repsonse!
> > The "smffixlinks&q
> > This was caused by an additional drive on his computer that for some reason
> > references back to his C drive. So the excel file was trying to find:
> > X/:ProgramFiles/
> > Go figure.
> >
> > Another quick question -
> > I tried to use the smfForceRecalculati
> > bosses. I updated the Internet settings per the "Links" section, but to no
> > avail. I also enabled all Macros.
> >
> > I have not been able to find a thread where changing the internet settins
> > did not work. Any thoughts?
> >
>
Sat Jul 27, 2013 8:03 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
It could be the other computer doesn't have the Ctrl+Shift+R keyboard
shortcut assigned to the macro. That assignment isn't done by the add-in.
But I don't know why nothing happens when you run it manually...
And, yes, if you're moving things back and forth, it may require
smfFixLinks if you use different folders for the add-in.
However, since you are using the same folder, that shouldn't happen. You
can check, with:
=RCHGetElementNumber("Version")
...which does echo the location of the add-in in addition to the version of
the add-in being used.
As for the add-in being disabled, I've seen that from others that use it in
a corporate environment, where their IT department has something that
disables all non-standard add-ins when EXCEL is started up (or when the
computer is booted up). Not sure if that would apply or not.
On Sat, Jul 27, 2013 at 6:55 AM, quasiwasp <joannapalmerjunk@gmail.com >wrote:
> "You say it does not work. What did you expect it to do, and what results
> did you see?"
>
> On my computer, when i use Ctrl + Shift + R all stock prices in the
> workbook using "RCHGetYahooQuotes() update to the current prices. On other
> computers, nothing happens when I use the shortcut. I tried running the
> smfForceRecalculation manually, but nothing happened. When I clicked a
> particular cell containing the "RCHGetYahooQuotes() formula and hit enter
> on computers other than my own, the stock price updates.
>
> As for the smfFixLinks, each time I close the sheet on my bosses computer,
> open the sheet on my computer the Fix Links Macro is the only thing that
> seems to correct the "NAME?" errors in the sheet,
>
> If I've opened, saved and closed the sheet on my computer, then open it on
> my bosses computer, the add is no longer active on my bosses computer. It
> still shows up on the active Add in list. But in order to get it to work
> (any function invovled with it) I have to go back to the Excel Add In,
> Browse, and remap (if that's the right terminology) the add in to his excel.
>
> On all of these computers, the add in Folder is located in C:\Program
> Files\SMF Add in.
>
> There are 3 computers in use. These problems only arise when the add is
> used on 1 of the computers and then used on one of the other 2 computers.
> Computers 2 and 3 can both use the add in and the spreadsheet on Dropbox
> without any of these issues. I'm terribly confused by all of it...
>
> Thanks so much again for the help.
>
shortcut assigned to the macro. That assignment isn't done by the add-in.
But I don't know why nothing happens when you run it manually...
And, yes, if you're moving things back and forth, it may require
smfFixLinks if you use different folders for the add-in.
However, since you are using the same folder, that shouldn'
can check, with:
=RCHGetElementNumbe
...which does echo the location of the add-in in addition to the version of
the add-in being used.
As for the add-in being disabled, I've seen that from others that use it in
a corporate environment, where their IT department has something that
disables all non-standard add-ins when EXCEL is started up (or when the
computer is booted up). Not sure if that would apply or not.
On Sat, Jul 27, 2013 at 6:55 AM, quasiwasp <joannapalmerjunk@
> "You say it does not work. What did you expect it to do, and what results
> did you see?"
>
> On my computer, when i use Ctrl + Shift + R all stock prices in the
> workbook using "RCHGetYahooQu
> computers, nothing happens when I use the shortcut. I tried running the
> smfForceRecalculati
> particular cell containing the "RCHGetYahooQu
> on computers other than my own, the stock price updates.
>
> As for the smfFixLinks, each time I close the sheet on my bosses computer,
> open the sheet on my computer the Fix Links Macro is the only thing that
> seems to correct the "NAME?" errors in the sheet,
>
> If I've opened, saved and closed the sheet on my computer, then open it on
> my bosses computer, the add is no longer active on my bosses computer. It
> still shows up on the active Add in list. But in order to get it to work
> (any function invovled with it) I have to go back to the Excel Add In,
> Browse, and remap (if that's the right terminology) the add in to his excel.
>
> On all of these computers, the add in Folder is located in C:\Program
> Files\SMF Add in.
>
> There are 3 computers in use. These problems only arise when the add is
> used on 1 of the computers and then used on one of the other 2 computers.
> Computers 2 and 3 can both use the add in and the spreadsheet on Dropbox
> without any of these issues. I'm terribly confused by all of it...
>
> Thanks so much again for the help.
>
Sat Jul 27, 2013 1:18 pm (PDT) . Posted by:
"Joel" joelfp2000
Randy,
This is AWESOME! Thank you so much for your help with this.
Joel
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> For complete tables:
>
> =RCHGetHTMLTable("
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=PDVYX ","Bond
> Quality",-1,"",2)
> =RCHGetHTMLTable("
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=PDVYX ","Sector
> Weightings",-1,"",2)
>
> Otherwise, something like these for the individual items:
>
> =RCHGetTableCell("
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=PDVYX ",
> 1,"Bond Quality",">AAA<")
> =RCHGetTableCell("
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=PDVYX ",
> 2,"Sector Weightings","Mortgage Pass-Thru")
>
> On Fri, Jul 26, 2013 at 4:28 PM, Joel <joel.andrews@...> wrote:
>
> > Ticker symbol would be PDVYX
> >
> > > On Thu, Jul 25, 2013 at 8:07 PM, Joel <joel.andrews@> wrote:
> > > >
> > > > Question, I am using the quicktake print report (=RCHGetTableCell("
> > > > http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=
> > "&$A2,1,"average
> > > > eff maturity"))to try to get the Bond Quality (eg. AAA, AA, A, BBB,
> > etc)
> > > > and also the Bond Sector Ratings (eg. Government, treasuries, tips, us
> > > > agency; Corporate, mortgage pass through, etc.). Do you have any idea
> > how
> > > > I can do this?
> >
>
This is AWESOME! Thank you so much for your help with this.
Joel
--- In smf_addin@yahoogrou
>
> For complete tables:
>
> =RCHGetHTMLTable(
> http://quicktake.
> Quality"
> =RCHGetHTMLTable(
> http://quicktake.
> Weightings"
>
> Otherwise, something like these for the individual items:
>
> =RCHGetTableCell(
> http://quicktake.
> 1,"Bond Quality"
> =RCHGetTableCell(
> http://quicktake.
> 2,"Sector Weightings"
>
> On Fri, Jul 26, 2013 at 4:28 PM, Joel <joel.andrews@
>
> > Ticker symbol would be PDVYX
> >
> > > On Thu, Jul 25, 2013 at 8:07 PM, Joel <joel.andrews@
> > > >
> > > > Question, I am using the quicktake print report (=RCHGetTableCell(
> > > > http://quicktake.
> > "&$A2,1,
> > > > eff maturity"
> > etc)
> > > > and also the Bond Sector Ratings (eg. Government, treasuries, tips, us
> > > > agency; Corporate, mortgage pass through, etc.). Do you have any idea
> > how
> > > > I can do this?
> >
>
Sat Jul 27, 2013 3:01 pm (PDT) . Posted by:
"okse26_26" okse26_26
I'm a total newbie with the SMF and of course not too great in Excel skills. Like a one person said, my newly created spreadsheet after downloading SMF, didn't update the next day. I followed your instructions about changing Internet options to toggle the appropriate radio button "Every time I visit the webpage". It still didn't update. So, now I'm here, because I don't know what formulas to write for macros after clicking Alt+F8. When I wrote "=rchgetelementnumber(A3,25)" for Last Price, either button doesn't activate or if it did, I'm told the reference is not valid.
- How do I save macros that each time I come back to the spreadsheet, do Alt+F8 and have everything updated?
- Does the list of macros under Alt+F8 need to include specific macro formulas for all chosen elements from the Definition file?
- I believe I'm doing something wrong, because it doesn't make sense to write a macro for each stock, does it?
Thanks in advance. Please read my PS below because I've got another problem with Internet settings.
PS. So, just for curiosity I went back to Control Panel->Internet Options->Under Browsing History & click Settings. For some reason, the radio button keeps reverting to "Automatically". It doesn't stay on "Every time I visit website". How to make it stay there? Do I need to make changes under "Delete" settings? Also, should the box be checked or unchecked under "Browsing History" called "Delete Browsing history on exit"?
Thank you in advance.
--- In smf_addin@yahoogroups.com , "Randy H" <rharmelink@...> wrote:
>
> Overview
>
> When the RCHGetElementNumber() and RCHGetTableCell() and RCHGetWebData()
> functions are invoked, they actually extract data from a copy of the web
> page that was saved by the add-in. This is done to make the functions
> run more quickly, because the actual retrieval of a web page from the
> Internet is the part of the process that takes the most time. For
> example, if you were to get 20 or 30 data items from the same web page,
> this process allows the web page to be retrieved once and then all
> extractions are done from that single retrieval of the web page from the
> Internet. Otherwise, each invocation of those functions would need to
> grab a new web page.
>
> In general, the process operates as follows:
>
> * The "saved array" is first checked, to see if the web page the data
> is being extracted from has already been retrieved from the Internet.
> * If an entry in the "saved array" is found, the data is extracted
> from the saved copy of the web page.
> * If an entry is not found in the "saved array", the source code of
> the web page is retrieved from the Internet and then put into the "saved
> array", which is indexed by URL. Then, the data is extracted from that
> saved copy of the web page.
> What does the smfForceRecalculation macro do?
>
> When you run this macro, it purges the "saved array" of web pages and
> then triggers a full recalculation within EXCEL. Since the "saved
> array" of web pages has been emptied out, the add-in functions that use
> the "saved array" of web pages will no longer find a saved copy of a web
> page, and be forced to retrieve a fresh copy of the web page. Note,
> however, that ONLY the first function invocation that uses a given URL
> will go to the Internet -- because it will save a new copy of the web
> page, which all other functions using that URL will use instead of going
> to the Internet to get the web page again.
>
> In addition, this macro will also force all of the RCHGetElementNumber()
> element definitions to be reloaded. That was actually the original
> reason the macro was written -- so I could modify an element definition
> and tell the add-in to reload the new element definitions and test them
> to see if the changes worked correctly.
>
> How do I run the smfForceRecalculation macro?
>
> You can use the keyboard shortcut alt-F8 to bring up EXCEL's macro
> dialog window. Unfortunately, this window does NOT list macros that are
> part of an add-in. That means you need to type in the macro name. When
> you complete entering the macro name, you should see the various buttons
> activate. You then click "Run" to run the macro.
>
> I have actually assigned the macro to a keyboard shortcut (done from the
> "Options..." button, which is located several buttons below the "Run"
> button used above). I also have added a button to my Quick Access
> Toolbar in EXCEL 2007 that runs the macro. It is what I use most often.
> In fact, I usually click on it each time before I open a new workbook,
> to reset the "saved array" of web pages for that workbook. But you
> probably don't want to do that if you have a number of workbooks open
> that use add-in functions, since the macro will force ALL add-in
> functions in ALL workbooks to recalculate.
>
> Additional Notes
>
> The RCHGetYahooQuotes() and RCHGetYahooHistory() functions do NOT use
> the "saved array" of web pages. That's because they both return an
> entire set of data with each invocation of the function. So there is no
> advantage to saving the web page -- since all possible data is extracted
> from a single Internet access. In fact, Yahoo is returning CSV files,
> not web pages. So the functions just need to parse the CSV files and
> place the data into the range they were given access to when the
> formulas were array-entered.
>
> Note, however, that the smfForceRecalculation macro WILL make those
> functions grab new copies of the CSV files and parse them out.
>
>
> PS: Additional "Frequently Asked Questions" and add-in Tips can be found
> in the "Links" area of the Yahoo group:
>
> http://finance.groups.yahoo.com/group/smf_addin/links
>
- How do I save macros that each time I come back to the spreadsheet, do Alt+F8 and have everything updated?
- Does the list of macros under Alt+F8 need to include specific macro formulas for all chosen elements from the Definition file?
- I believe I'm doing something wrong, because it doesn't make sense to write a macro for each stock, does it?
Thanks in advance. Please read my PS below because I've got another problem with Internet settings.
PS. So, just for curiosity I went back to Control Panel->Internet Options->
Thank you in advance.
--- In smf_addin@yahoogrou
>
> Overview
>
> When the RCHGetElementNumber
> functions are invoked, they actually extract data from a copy of the web
> page that was saved by the add-in. This is done to make the functions
> run more quickly, because the actual retrieval of a web page from the
> Internet is the part of the process that takes the most time. For
> example, if you were to get 20 or 30 data items from the same web page,
> this process allows the web page to be retrieved once and then all
> extractions are done from that single retrieval of the web page from the
> Internet. Otherwise, each invocation of those functions would need to
> grab a new web page.
>
> In general, the process operates as follows:
>
> * The "saved array" is first checked, to see if the web page the data
> is being extracted from has already been retrieved from the Internet.
> * If an entry in the "saved array" is found, the data is extracted
> from the saved copy of the web page.
> * If an entry is not found in the "saved array", the source code of
> the web page is retrieved from the Internet and then put into the "saved
> array", which is indexed by URL. Then, the data is extracted from that
> saved copy of the web page.
> What does the smfForceRecalculati
>
> When you run this macro, it purges the "saved array" of web pages and
> then triggers a full recalculation within EXCEL. Since the "saved
> array" of web pages has been emptied out, the add-in functions that use
> the "saved array" of web pages will no longer find a saved copy of a web
> page, and be forced to retrieve a fresh copy of the web page. Note,
> however, that ONLY the first function invocation that uses a given URL
> will go to the Internet -- because it will save a new copy of the web
> page, which all other functions using that URL will use instead of going
> to the Internet to get the web page again.
>
> In addition, this macro will also force all of the RCHGetElementNumber
> element definitions to be reloaded. That was actually the original
> reason the macro was written -- so I could modify an element definition
> and tell the add-in to reload the new element definitions and test them
> to see if the changes worked correctly.
>
> How do I run the smfForceRecalculati
>
> You can use the keyboard shortcut alt-F8 to bring up EXCEL's macro
> dialog window. Unfortunately, this window does NOT list macros that are
> part of an add-in. That means you need to type in the macro name. When
> you complete entering the macro name, you should see the various buttons
> activate. You then click "Run" to run the macro.
>
> I have actually assigned the macro to a keyboard shortcut (done from the
> "Options.
> button used above). I also have added a button to my Quick Access
> Toolbar in EXCEL 2007 that runs the macro. It is what I use most often.
> In fact, I usually click on it each time before I open a new workbook,
> to reset the "saved array" of web pages for that workbook. But you
> probably don't want to do that if you have a number of workbooks open
> that use add-in functions, since the macro will force ALL add-in
> functions in ALL workbooks to recalculate.
>
> Additional Notes
>
> The RCHGetYahooQuotes(
> the "saved array" of web pages. That's because they both return an
> entire set of data with each invocation of the function. So there is no
> advantage to saving the web page -- since all possible data is extracted
> from a single Internet access. In fact, Yahoo is returning CSV files,
> not web pages. So the functions just need to parse the CSV files and
> place the data into the range they were given access to when the
> formulas were array-entered.
>
> Note, however, that the smfForceRecalculati
> functions grab new copies of the CSV files and parse them out.
>
>
> PS: Additional "Frequently Asked Questions" and add-in Tips can be found
> in the "Links" area of the Yahoo group:
>
> http://finance.
>
Sat Jul 27, 2013 5:45 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Sat, Jul 27, 2013 at 2:22 PM, okse26_26 <okse26_26@yahoo.com > wrote:
>
> I'm a total newbie with the SMF and of course not too great in Excel
skills. Like a one person said, my newly created
> spreadsheet after downloading SMF, didn't update the next day. I followed
your instructions about changing Internet
> options to toggle the appropriate radio button "Every time I visit the
webpage". It still didn't update.
Not updating the NEXT day sounds very funky. I don't think that would be an
IE cache problem. But it could be the result of the add-in having saved the
page the prior day, if you didn't exit and restart EXCEL (then it would
still be saved in the VBA environment).
> So, now I'm here, because I don't know what formulas to write for macros
after clicking Alt+F8.
I'm confused by this statement. Alt+F8 is how to get to where you can run a
macro. But "what formulas to write" is what I don't understand. There is no
one-to-one correspondence. The smfForceRecalculation is for the SMF
environment, not for each individual formula.
> When I wrote "=rchgetelementnumber(A3,25)" for Last Price, either button
doesn't activate or if it did, I'm told the
> reference is not valid.
The formula is a formula, there would be no button to activate it. You
would need to have the add-in installed.
And, BTW, you should use the RCHGetYahooQuotes() function for quotes.
Element #25 is from MSN. When they redesigned their web page last year,
they changed their meaning of "Last Price" and started posting the previous
day's closing price. So it won't update very often, just once per trading
day.
> - How do I save macros that each time I come back to the spreadsheet, do
Alt+F8 and have everything updated?
I have the smfForceRecalculation macro assigned to a button on my Quick
Access Toolbar in EXCEL 2007. In between using workbooks (i.e. no SMF
workbooks open), I usually run the macro just to reset everything. Then,
when I open the next workbook, I won't need to run the
smfForceRecalculation macro, because the new workbook is starting out fresh.
And, typically, different workbooks don't use the same web page
extractions, so it quite often isn't necessary. But I've made it a habit to
do so.
Really, the main reason for the smfForceRecalculation macro is to tell the
add-in to purge all saved web page. This forces functions to retrieve a
fresh copy of a web page.
But, for example, if you start up EXCEL and open an SMF workbook,
everything SHOULD be updated and you don't need to run the
smfForceRecalculation macro. Because the fact that you just opened EXCEL
means the VBA environment has no saved web pages. In essence, closing and
restarting EXCEL does the same thing as smfForceRecalculation, as far the
SMF add-in is concerned. Because closing and restarting EXCEL also resets
the VBA environment.
> - Does the list of macros under Alt+F8 need to include specific macro
formulas for all chosen elements from the
> Definition file?
No. They have no correspondence to each other.
> - I believe I'm doing something wrong, because it doesn't make sense to
write a macro for each stock, does it?
Correct. The smfForceRecalculation macro is just a way to get rid of saved
web pages, so that when the formulas recalculate, they will get a fresh
copy of a web page.
When you write a formula like:
=RCHGetElementNumber("MMM";, 25)
...the add-in checks to see if the web page containing that data element
has already been retrieved and saved in the VBA environment. If it has, it
extracts the data element from the saved copy of the web page. If it
hasn't, it retrieves and saves the source code of the web page, and then
extracts it.
The reason it does this is that you may be extracting dozens of data
elements from a single web page. This process means the web page only needs
to be retrieved once. If the web page had to be retrieved for every single
data element that is being extracted, the add-in would be VERY slow.
That's why the smfForceRecalculation macro exists -- so that there is a way
to get rid of stale saved web pages and force a new one to be retrieved for
extractions of data elements.
> PS. So, just for curiosity I went back to Control Panel->Internet
Options->Under Browsing History & click Settings.
> For some reason, the radio button keeps reverting to "Automatically". It
doesn't stay on "Every time I visit website".
> How to make it stay there? Do I need to make changes under "Delete"
settings? Also, should the box be checked or
> unchecked under "Browsing History" called "Delete Browsing history on
exit"?
I usually delete my browsing history on exit, but that's a personal
preference. It's unrelated to the add-in. You can choose whatever you'd
like.
The reason for needing "Every time I visit a website" is that IE typically
does something similar to the add-in, saving web pages, and using the saved
copies of the web page if you visit it again. The "Every time I visit a
website" overrides that processing and tells it to ALWAYS get a fresh copy
of the web page. EXCEL and the add-in use the IE cookies and "Temporary
Internet Files" because they all use the same MicroSoft Internet access
processing.
As to why the radio button keeps reverting back, I have no idea. Are you in
some type of corporate environment where they would reset all of the
options back to some corporate standard? Otherwise, no clue.
>
> I'm a total newbie with the SMF and of course not too great in Excel
skills. Like a one person said, my newly created
> spreadsheet after downloading SMF, didn't update the next day. I followed
your instructions about changing Internet
> options to toggle the appropriate radio button "Every time I visit the
webpage"
Not updating the NEXT day sounds very funky. I don't think that would be an
IE cache problem. But it could be the result of the add-in having saved the
page the prior day, if you didn't exit and restart EXCEL (then it would
still be saved in the VBA environment)
> So, now I'm here, because I don't know what formulas to write for macros
after clicking Alt+F8.
I'm confused by this statement. Alt+F8 is how to get to where you can run a
macro. But "what formulas to write" is what I don't understand. There is no
one-to-one correspondence. The smfForceRecalculati
environment, not for each individual formula.
> When I wrote "=rchgetelemen
doesn't activate or if it did, I'm told the
> reference is not valid.
The formula is a formula, there would be no button to activate it. You
would need to have the add-in installed.
And, BTW, you should use the RCHGetYahooQuotes(
Element #25 is from MSN. When they redesigned their web page last year,
they changed their meaning of "Last Price" and started posting the previous
day's closing price. So it won't update very often, just once per trading
day.
> - How do I save macros that each time I come back to the spreadsheet, do
Alt+F8 and have everything updated?
I have the smfForceRecalculati
Access Toolbar in EXCEL 2007. In between using workbooks (i.e. no SMF
workbooks open), I usually run the macro just to reset everything. Then,
when I open the next workbook, I won't need to run the
smfForceRecalculati
And, typically, different workbooks don't use the same web page
extractions, so it quite often isn't necessary. But I've made it a habit to
do so.
Really, the main reason for the smfForceRecalculati
add-in to purge all saved web page. This forces functions to retrieve a
fresh copy of a web page.
But, for example, if you start up EXCEL and open an SMF workbook,
everything SHOULD be updated and you don't need to run the
smfForceRecalculati
means the VBA environment has no saved web pages. In essence, closing and
restarting EXCEL does the same thing as smfForceRecalculati
SMF add-in is concerned. Because closing and restarting EXCEL also resets
the VBA environment.
> - Does the list of macros under Alt+F8 need to include specific macro
formulas for all chosen elements from the
> Definition file?
No. They have no correspondence to each other.
> - I believe I'm doing something wrong, because it doesn't make sense to
write a macro for each stock, does it?
Correct. The smfForceRecalculati
web pages, so that when the formulas recalculate, they will get a fresh
copy of a web page.
When you write a formula like:
=RCHGetElementNumbe
...the add-in checks to see if the web page containing that data element
has already been retrieved and saved in the VBA environment. If it has, it
extracts the data element from the saved copy of the web page. If it
hasn't, it retrieves and saves the source code of the web page, and then
extracts it.
The reason it does this is that you may be extracting dozens of data
elements from a single web page. This process means the web page only needs
to be retrieved once. If the web page had to be retrieved for every single
data element that is being extracted, the add-in would be VERY slow.
That's why the smfForceRecalculati
to get rid of stale saved web pages and force a new one to be retrieved for
extractions of data elements.
> PS. So, just for curiosity I went back to Control Panel->Internet
Options->
> For some reason, the radio button keeps reverting to "Automatically
doesn't stay on "Every time I visit website"
> How to make it stay there? Do I need to make changes under "Delete"
settings? Also, should the box be checked or
> unchecked under "Browsing History" called "Delete Browsing history on
exit"?
I usually delete my browsing history on exit, but that's a personal
preference. It's unrelated to the add-in. You can choose whatever you'd
like.
The reason for needing "Every time I visit a website" is that IE typically
does something similar to the add-in, saving web pages, and using the saved
copies of the web page if you visit it again. The "Every time I visit a
website" overrides that processing and tells it to ALWAYS get a fresh copy
of the web page. EXCEL and the add-in use the IE cookies and "Temporary
Internet Files" because they all use the same MicroSoft Internet access
processing.
As to why the radio button keeps reverting back, I have no idea. Are you in
some type of corporate environment where they would reset all of the
options back to some corporate standard? Otherwise, no clue.
Sat Jul 27, 2013 10:59 pm (PDT) . Posted by:
"Gururaj Rao" gurujapan
Randy,
Thanks for your prompt response.
On Sat, Jul 27, 2013 at 10:32 AM, Randy Harmelink <rharmelink@gmail.com >wrote:
>
>
>
> 1. You really need to look at the source code of the web page and see
> if the data is there. I use this template from the FILES area to play
> around with a new web page to see what's there:
>
> smfGetTagContent-Quick-Webpage-Examination.xls
>
> For example, the "By HTML Tag" can show you all of the tables on the
> web page. Or, you could try to position yourself at the top of a table, and
> examine all the rows.
>
>
OK. I will play around with the file above.
>
> 1.
> 2. The most commonly used function for custom extractions is the
> RCHGetTableCell() function. For example, on that first URL you cite:
>
> =RCHGetTableCell("
> http://money.livemint.com/IID63/F107685/Valuation/Ratios/Company.aspx ",1,">BSE
> Code")
>
> ...will extract the BSE Code. That's about as simple as that function
> gets. Or:
>
> =RCHGetTableCell("
> http://money.livemint.com/IID63/F107685/Valuation/Ratios/Company.aspx ",1,"Total
> Debt/Equity(%)")
>
> ...varying that 1 from 1 to 5 would get you the five years of the
> Total Debt/Equity ratio.
>
>
The above seem to work fine when the values are adjacent that is,for
example:
Earnings Per Share (Rs) 22.94
What happens to the formula if the value is below like:
*Last Price*
382.806.
If I use: =RCHGetTableCell("
http://money.livemint.com/IID42/F107685/QuickQuote/Company.aspx ",1,">Last
Price")
The value returned is "Today's Change", the header of the adjacent column
and not the value of Last Price.
I removed the ">" which seems to indicate the value of the next column; but
this also returns "Today's Change."
I should find the equivalent of the down arrow in the formula I guess.
>
> 1.
>
> If you do a lot of extractions from a page, or multiple related pages, it
> would be very helpful to upload some type of template showing all of the
> formulas to the "Uploads by Forum Members" folder. Then others can use the
> formulas without needing to reinvent the wheel.
>
Yes, I will definitely do so. Right now I am struggling with the basics.
For instance, if I extract data from cells in tables for a specific company
using the above functions such as:
=RCHGetTableCell("
http://money.livemint.com/IID63/F107685/Valuation/Ratios/Company.aspx ",1,"Total
Debt/Equity(%)")
which include the URL, how can I link the company URL ID (such as F107685)
to the ticker symbol (or NSE Code in my case) so that the data capture will
work for other companies as well by merely entering the NSE Code (such as
WIPRO, BATAINDIA and so on?)
Thanks in advance for any hints you can offer.
Gururaj Rao
Thanks for your prompt response.
On Sat, Jul 27, 2013 at 10:32 AM, Randy Harmelink <rharmelink@gmail.
>
>
>
> 1. You really need to look at the source code of the web page and see
> if the data is there. I use this template from the FILES area to play
> around with a new web page to see what's there:
>
> smfGetTagContent-
>
> For example, the "By HTML Tag" can show you all of the tables on the
> web page. Or, you could try to position yourself at the top of a table, and
> examine all the rows.
>
>
OK. I will play around with the file above.
>
> 1.
> 2. The most commonly used function for custom extractions is the
> RCHGetTableCell(
>
> =RCHGetTableCell(
> http://money.
> Code")
>
> ...will extract the BSE Code. That's about as simple as that function
> gets. Or:
>
> =RCHGetTableCell(
> http://money.
> Debt/Equity(
>
> ...varying that 1 from 1 to 5 would get you the five years of the
> Total Debt/Equity ratio.
>
>
The above seem to work fine when the values are adjacent that is,for
example:
Earnings Per Share (Rs) 22.94
What happens to the formula if the value is below like:
*Last Price*
382.806.
If I use: =RCHGetTableCell(
http://money.
Price")
The value returned is "Today&#
and not the value of Last Price.
I removed the ">" which seems to indicate the value of the next column; but
this also returns "Today&#
I should find the equivalent of the down arrow in the formula I guess.
>
> 1.
>
> If you do a lot of extractions from a page, or multiple related pages, it
> would be very helpful to upload some type of template showing all of the
> formulas to the "Uploads by Forum Members" folder. Then others can use the
> formulas without needing to reinvent the wheel.
>
Yes, I will definitely do so. Right now I am struggling with the basics.
For instance, if I extract data from cells in tables for a specific company
using the above functions such as:
=RCHGetTableCell(
http://money.
Debt/Equity(
which include the URL, how can I link the company URL ID (such as F107685)
to the ticker symbol (or NSE Code in my case) so that the data capture will
work for other companies as well by merely entering the NSE Code (such as
WIPRO, BATAINDIA and so on?)
Thanks in advance for any hints you can offer.
Gururaj Rao
Sun Jul 28, 2013 12:04 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Sat, Jul 27, 2013 at 10:59 PM, Gururaj Rao <honyakusha@gmail.com > wrote:
>
> What happens to the formula if the value is below like:
> *Last Price*
> 382.806.
>
> If I use: =RCHGetTableCell("
> http://money.livemint.com/IID42/F107685/QuickQuote/Company.aspx ",1,">Last
> Price")
> The value returned is "Today's Change", the header of the adjacent column
> and not the value of Last Price.
> I removed the ">" which seems to indicate the value of the next column;
> but this also returns "Today's Change."
> I should find the equivalent of the down arrow in the formula I guess.
>
There's no set rule for that. You have to look to see how it's coded on the
web page. Sometimes they are even in the same table cell, which means you
have to retrieve the table cell and parse the data out -- usually with the
smfStrExtr() function.
In other cases, those may each be on a row of a table. Then you need to use
the "pRows" parameter of the RCHGetTableCell() function to tell it to skip
a row and enough cells on that new row to get to the value.
Sometimes they're coded using "div" and "span" HTML tags, in which case you
need to use the smfGetTagContent() function.
Yes, I will definitely do so. Right now I am struggling with the basics.
> For instance, if I extract data from cells in tables for a specific
> company using the above functions such as:
> =RCHGetTableCell("
> http://money.livemint.com/IID63/F107685/Valuation/Ratios/Company.aspx ",1,"Total
> Debt/Equity(%)")
>
> which include the URL, how can I link the company URL ID (such as F107685)
> to the ticker symbol (or NSE Code in my case) so that the data capture will
> work for other companies as well by merely entering the NSE Code (such as
> WIPRO, BATAINDIA and so on?)
>
You would need to concatenate strings together, which is a normal EXCEL
operation, to create the URL.
For example, if the F107685 is in cell A2, then this:
"http://money.livemint.com/IID63/F107685/Valuation/Ratios/Company.aspx "
...can be this:
="http://money.livemint.com/IID63/ " & A2 & "/Valuation/Ratios/Company.aspx"
>
> What happens to the formula if the value is below like:
> *Last Price*
> 382.806.
>
> If I use: =RCHGetTableCell(
> http://money.
> Price")
> The value returned is "Today&#
> and not the value of Last Price.
> I removed the ">" which seems to indicate the value of the next column;
> but this also returns "Today&#
> I should find the equivalent of the down arrow in the formula I guess.
>
There's no set rule for that. You have to look to see how it's coded on the
web page. Sometimes they are even in the same table cell, which means you
have to retrieve the table cell and parse the data out -- usually with the
smfStrExtr() function.
In other cases, those may each be on a row of a table. Then you need to use
the "pRows" parameter of the RCHGetTableCell(
a row and enough cells on that new row to get to the value.
Sometimes they're coded using "div" and "span" HTML tags, in which case you
need to use the smfGetTagContent(
Yes, I will definitely do so. Right now I am struggling with the basics.
> For instance, if I extract data from cells in tables for a specific
> company using the above functions such as:
> =RCHGetTableCell(
> http://money.
> Debt/Equity(
>
> which include the URL, how can I link the company URL ID (such as F107685)
> to the ticker symbol (or NSE Code in my case) so that the data capture will
> work for other companies as well by merely entering the NSE Code (such as
> WIPRO, BATAINDIA and so on?)
>
You would need to concatenate strings together, which is a normal EXCEL
operation, to create the URL.
For example, if the F107685 is in cell A2, then this:
"http://money.
...can be this:
="http://money.
Sun Jul 28, 2013 12:09 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Forgot to give you an example of the solution for this, which uses the
pRows parameter:
=RCHGetTableCell("
http://money.livemint.com/IID42/F107685/QuickQuote/Company.aspx ",1,">Last
Price",,,,1)
...so it positions itself based on the ">Last Price" search string, then
skips to the next row (i.e. the last "1" parameter), and then picks up the
value in the first column of that row (i.e. the first "1" parameter).
If you only need to go to the next row, you could also have done:
=RCHGetTableCell("
http://money.livemint.com/IID42/F107685/QuickQuote/Company.aspx ",1,">Last
Price","<tr")
...which searches first for the ">Last Price" string, then the next "<tr"
string after that (i.e. usually the next row of the table), and then picks
up the first table cell it finds after that.
On Sat, Jul 27, 2013 at 10:59 PM, Gururaj Rao <honyakusha@gmail.com > wrote:
> What happens to the formula if the value is below like:
> *Last Price*
> 382.806.
>
> If I use: =RCHGetTableCell("
> http://money.livemint.com/IID42/F107685/QuickQuote/Company.aspx ",1,">Last
> Price")
> The value returned is "Today's Change", the header of the adjacent column
> and not the value of Last Price.
> I removed the ">" which seems to indicate the value of the next column;
> but this also returns "Today's Change."
> I should find the equivalent of the down arrow in the formula I guess.
>
pRows parameter:
=RCHGetTableCell(
http://money.
Price",
...so it positions itself based on the ">Last Price" search string, then
skips to the next row (i.e. the last "1" parameter), and then picks up the
value in the first column of that row (i.e. the first "1" parameter).
If you only need to go to the next row, you could also have done:
=RCHGetTableCell(
http://money.
Price",
...which searches first for the ">Last Price" string, then the next "<tr"
string after that (i.e. usually the next row of the table), and then picks
up the first table cell it finds after that.
On Sat, Jul 27, 2013 at 10:59 PM, Gururaj Rao <honyakusha@gmail.
> What happens to the formula if the value is below like:
> *Last Price*
> 382.806.
>
> If I use: =RCHGetTableCell(
> http://money.
> Price")
> The value returned is "Today&#
> and not the value of Last Price.
> I removed the ">" which seems to indicate the value of the next column;
> but this also returns "Today&#
> I should find the equivalent of the down arrow in the formula I guess.
>
Tidak ada komentar:
Posting Komentar