14 New Messages
Digest #2596
4b
Re: Macro to to avoid unnecessary downloads & recalculation [1 Attac by "Randy Harmelink" rharmelink
Messages
Fri Apr 26, 2013 5:26 am (PDT) . Posted by:
"weekeewawa" weekeewawa
hello randy
i tried this code
=RCHGetHTMLTable("http://data.worldbank.org/indicator/CM.MKT.LCAP.CD ",">Country Name",-1,"",1)
but it shows an empty content...
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> No element numbers. But the data is easily extracted with RCHGetHTMLTable():
>
> *Web Page:* http://data.worldbank.org/indicator/CM.MKT.LCAP.CD *Begin
> String:* Country Name *Begin Direction:* -1 *End String:*
> *End Direction:* 1
>
> On Thu, Apr 25, 2013 at 9:13 PM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > do you have any codes to extract data from this webpage,
> >
> > http://data.worldbank.org/indicator/CM.MKT.LCAP.CD
> >
> >
>
i tried this code
=RCHGetHTMLTable(
but it shows an empty content...
--- In smf_addin@yahoogrou
>
> No element numbers. But the data is easily extracted with RCHGetHTMLTable(
>
> *Web Page:* http://data.
> String:* Country Name *Begin Direction:* -1 *End String:*
> *End Direction:* 1
>
> On Thu, Apr 25, 2013 at 9:13 PM, weekeewawa <weekeewawa@
>
> >
> > do you have any codes to extract data from this webpage,
> >
> > http://data.
> >
> >
>
Fri Apr 26, 2013 5:34 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Drop the ">" from ">Country Name".
On Fri, Apr 26, 2013 at 5:26 AM, weekeewawa <weekeewawa@yahoo.com > wrote:
>
> i tried this code
>
> =RCHGetHTMLTable("http://data.worldbank.org/indicator/CM.MKT.LCAP.CD ",">Country
> Name",-1,"",1)
>
> but it shows an empty content...
>
On Fri, Apr 26, 2013 at 5:26 AM, weekeewawa <weekeewawa@yahoo.
>
> i tried this code
>
> =RCHGetHTMLTable(
> Name",-
>
> but it shows an empty content...
>
Fri Apr 26, 2013 6:51 am (PDT) . Posted by:
"weekeewawa" weekeewawa
works well!
thanks randy
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Drop the ">" from ">Country Name".
>
> On Fri, Apr 26, 2013 at 5:26 AM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > i tried this code
> >
> > =RCHGetHTMLTable("http://data.worldbank.org/indicator/CM.MKT.LCAP.CD ",">Country
> > Name",-1,"",1)
> >
> > but it shows an empty content...
> >
>
thanks randy
--- In smf_addin@yahoogrou
>
> Drop the ">" from ">Country Name".
>
> On Fri, Apr 26, 2013 at 5:26 AM, weekeewawa <weekeewawa@
>
> >
> > i tried this code
> >
> > =RCHGetHTMLTable(
> > Name",-
> >
> > but it shows an empty content...
> >
>
Fri Apr 26, 2013 6:31 am (PDT) . Posted by:
"weekeewawa" weekeewawa
works well!
thanks randy
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Get rid of the trailing dollar sign from what you retrieve...
>
> Just drop the "0+" from the formula, and you'll see what gets returned. If
> it's not numeric, you'll get a #VALUE! error when you try to add 0 to it.
>
> Or, you can use the smfConvertData() function instead of adding 0 to
> convert it to numeric:
>
> =smfConvertData(SUBSTITUTE(RCHGetTableCell("http://www.4-traders.com/
> "&C2&"/quotes/",
> 1,"Session exchange")," ",""))
>
> On Thu, Apr 25, 2013 at 11:40 PM, weekeewawa <weekeewawa@...> wrote:
>
> > hello randy, while extracting 4-traders data for this webpage
> >
> > http://www.4-traders.com/INTERNATIONAL-BUSINESS-MA-4828/quotes/
> >
> > Session exchange 902 246 866$
> > Avg. Exchange 20 sessions 979 378 017$
> >
> > shows a #VALUE error
> > the code which i put is
> >
> > =0+SUBSTITUTE(RCHGetTableCell("http://www.4-traders.com/ "&C2&"/quotes/",1,"Session
> > exchange")," ","")
> >
> >
> > =0+SUBSTITUTE(RCHGetTableCell("http://www.4-traders.com/ "&C2&"/quotes/",1,"Avg.
> > Exchange 20 sessions")," ","")
> >
> > any solution to that?
> >
>
thanks randy
--- In smf_addin@yahoogrou
>
> Get rid of the trailing dollar sign from what you retrieve...
>
> Just drop the "0+" from the formula, and you'll see what gets returned. If
> it's not numeric, you'll get a #VALUE! error when you try to add 0 to it.
>
> Or, you can use the smfConvertData(
> convert it to numeric:
>
> =smfConvertData(
> "&C2&
> 1,"Session exchange"
>
> On Thu, Apr 25, 2013 at 11:40 PM, weekeewawa <weekeewawa@
>
> > hello randy, while extracting 4-traders data for this webpage
> >
> > http://www.4-
> >
> > Session exchange 902 246 866$
> > Avg. Exchange 20 sessions 979 378 017$
> >
> > shows a #VALUE error
> > the code which i put is
> >
> > =0+SUBSTITUTE(
> > exchange"
> >
> >
> > =0+SUBSTITUTE(
> > Exchange 20 sessions"
> >
> > any solution to that?
> >
>
Fri Apr 26, 2013 3:24 pm (PDT) . Posted by:
"invesco88" invesco88
I'm trying to use the smfGetCSVFile for Barchart's Traders Cheat Sheet. I've created a free account and logged in and it will let me download the CSV file in the browser but when I attempt to import it into a spreadsheet using the formula below it returns a bunch of HTML code.
smfGetCSVFile("http://www.barchart.com/cheatsheet.php?sym=PDFS&;export=.csv ")
Anyone having similar problem or have any suggestions for a solution?
Thanks.
smfGetCSVFile(
Anyone having similar problem or have any suggestions for a solution?
Thanks.
Fri Apr 26, 2013 5:05 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm not sure what to tell you. I copied and pasted that formula into a
worksheet, and it worked fine. I also changed the ticker symbol a few
times, and each of those worked fine.
Maybe the HTML code contains an error message of some type, that could
explain the situation?
On Fri, Apr 26, 2013 at 3:24 PM, invesco88 <franklin.forward@gmail.com >wrote:
> I'm trying to use the smfGetCSVFile for Barchart's Traders Cheat Sheet.
> I've created a free account and logged in and it will let me download the
> CSV file in the browser but when I attempt to import it into a spreadsheet
> using the formula below it returns a bunch of HTML code.
>
> smfGetCSVFile("http://www.barchart.com/cheatsheet.php?sym=PDFS&;export=.csv
> ")
>
> Anyone having similar problem or have any suggestions for a solution?
>
worksheet, and it worked fine. I also changed the ticker symbol a few
times, and each of those worked fine.
Maybe the HTML code contains an error message of some type, that could
explain the situation?
On Fri, Apr 26, 2013 at 3:24 PM, invesco88 <franklin.forward@
> I'm trying to use the smfGetCSVFile for Barchart'
> I've created a free account and logged in and it will let me download the
> CSV file in the browser but when I attempt to import it into a spreadsheet
> using the formula below it returns a bunch of HTML code.
>
> smfGetCSVFile(
> ")
>
> Anyone having similar problem or have any suggestions for a solution?
>
Fri Apr 26, 2013 5:00 pm (PDT) . Posted by:
"Amit Chaudhary" amitc@ymail.com
Hi,
If you notice your spreadsheets as they grow large, take longer to load, it might be due to downloading of data, recalculation, etc, this is particularly heavy for xls with MA & other history downloads..
I noticed that copying all, but first & last row as values makes it much better.
To simplify this, I use a Macro, assign it to a button and then select rows and press the button. The content of these rows are pasted back as values.
Simple, but useful. A xls is attached which has the button & macro & some rows of example SMF calls.
Thanks for Randy's work on SMF-Addin, some of the excel work has been eased for many of us, this is a small way of returning back.
Regards
Amit
If you notice your spreadsheets as they grow large, take longer to load, it might be due to downloading of data, recalculation, etc, this is particularly heavy for xls with MA & other history downloads..
I noticed that copying all, but first & last row as values makes it much better.
To simplify this, I use a Macro, assign it to a button and then select rows and press the button. The content of these rows are pasted back as values.
Simple, but useful. A xls is attached which has the button & macro & some rows of example SMF calls.
Thanks for Randy's work on SMF-Addin, some of the excel work has been eased for many of us, this is a small way of returning back.
Regards
Amit
Attachments with this message:
1 of 1 File(s)
Fri Apr 26, 2013 5:19 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Your workbook would update a lot faster if you used an array-entered
smfPricesByDates(). Your current workbook is doing 80 Internet accesses,
when it can be done with only 10.
For example, B3 thru B10 could be this formula:
=TRANSPOSE(smfPricesByDates(B$1,$A$3:$A$10))
Then, you could even use your technique and copy columns rightward instead
of rows downward.
On Fri, Apr 26, 2013 at 4:19 PM, Amit Chaudhary <amitc@ymail.com > wrote:
>
> If you notice your spreadsheets as they grow large, take longer to load,
> it might be due to downloading of data, recalculation, etc, this is
> particularly heavy for xls with MA & other history downloads..
>
> I noticed that copying all, but first & last row as values makes it much
> better.
> To simplify this, I use a Macro, assign it to a button and then select
> rows and press the button. The content of these rows are pasted back as
> values.
> Simple, but useful. A xls is attached which has the button & macro & some
> rows of example SMF calls.
>
> Thanks for Randy's work on SMF-Addin, some of the excel work has been
> eased for many of us, this is a small way of returning back.
>
>
smfPricesByDates(
when it can be done with only 10.
For example, B3 thru B10 could be this formula:
=TRANSPOSE(smfPrice
Then, you could even use your technique and copy columns rightward instead
of rows downward.
On Fri, Apr 26, 2013 at 4:19 PM, Amit Chaudhary <amitc@ymail.
>
> If you notice your spreadsheets as they grow large, take longer to load,
> it might be due to downloading of data, recalculation, etc, this is
> particularly heavy for xls with MA & other history downloads..
>
> I noticed that copying all, but first & last row as values makes it much
> better.
> To simplify this, I use a Macro, assign it to a button and then select
> rows and press the button. The content of these rows are pasted back as
> values.
> Simple, but useful. A xls is attached which has the button & macro & some
> rows of example SMF calls.
>
> Thanks for Randy's work on SMF-Addin, some of the excel work has been
> eased for many of us, this is a small way of returning back.
>
>
Fri Apr 26, 2013 5:01 pm (PDT) . Posted by:
"the_stock_studier" the_stock_studier
I'm a junior in college majoring in Finance, and I am diving into security analysis. This Excel add-in seems like a wonderful application, but I am having some problems.
I downloaded the zip file, and moved it to the correct location: C/program files/smf add-in.
I have Excel 2007, so I went under file/options/add-ins but smf_addin is not there. I don't see a link where I can browse for the saved files. How do I put smf_addin under my "Active Application Add-ins"?
If I open up the excel file "RCH_Stock_Market_Functions", which was downloaded from zip file, and start typing some UDF, all possible functions pop up, which is a good sign. For example, I followed the syntax for =RCHGetYahooHistory("CCMP";), but after I pressed enter, Excel froze.
What am I doing wrong? What am I missing? All I want to do is obtain historical quotes/prices from individual stocks, indexes and commodities to run regressions.
I downloaded the zip file, and moved it to the correct location: C/program files/smf add-in.
I have Excel 2007, so I went under file/options/
If I open up the excel file "RCH_Stock_
What am I doing wrong? What am I missing? All I want to do is obtain historical quotes/prices from individual stocks, indexes and commodities to run regressions.
Fri Apr 26, 2013 5:07 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sounds like you're not installing the add-in. Take a look at the
installation documentation in the "Documentation" folder in the FILES area
of the group
Also, there are some independent step-by-step installation instructions in
the LINKS area of the group.
On Fri, Apr 26, 2013 at 4:02 PM, the_stock_studier <phennein@gmail.com >wrote:
> I'm a junior in college majoring in Finance, and I am diving into security
> analysis. This Excel add-in seems like a wonderful application, but I am
> having some problems.
>
> I downloaded the zip file, and moved it to the correct location: C/program
> files/smf add-in.
>
> I have Excel 2007, so I went under file/options/add-ins but smf_addin is
> not there. I don't see a link where I can browse for the saved files. How
> do I put smf_addin under my "Active Application Add-ins"?
>
> If I open up the excel file "RCH_Stock_Market_Functions", which was
> downloaded from zip file, and start typing some UDF, all possible functions
> pop up, which is a good sign. For example, I followed the syntax for
> =RCHGetYahooHistory("CCMP";), but after I pressed enter, Excel froze.
>
> What am I doing wrong? What am I missing? All I want to do is obtain
> historical quotes/prices from individual stocks, indexes and commodities to
> run regressions.
>
installation documentation in the "Documentation
of the group
Also, there are some independent step-by-step installation instructions in
the LINKS area of the group.
On Fri, Apr 26, 2013 at 4:02 PM, the_stock_studier <phennein@gmail.
> I'm a junior in college majoring in Finance, and I am diving into security
> analysis. This Excel add-in seems like a wonderful application, but I am
> having some problems.
>
> I downloaded the zip file, and moved it to the correct location: C/program
> files/smf add-in.
>
> I have Excel 2007, so I went under file/options/
> not there. I don't see a link where I can browse for the saved files. How
> do I put smf_addin under my "Active Application Add-ins"
>
> If I open up the excel file "RCH_Stock_
> downloaded from zip file, and start typing some UDF, all possible functions
> pop up, which is a good sign. For example, I followed the syntax for
> =RCHGetYahooHistory
>
> What am I doing wrong? What am I missing? All I want to do is obtain
> historical quotes/prices from individual stocks, indexes and commodities to
> run regressions.
>
Fri Apr 26, 2013 5:07 pm (PDT) . Posted by:
"zoo62strat" zoo62strat
I am wondering if there is a way to get end of day info for stocks earlier than I presently do. Currently this function gives me updated daily data sometime after 9:00 pm. It would be nice to get it at market close, or even incomplete daily data before market close. Is this possible?
Fri Apr 26, 2013 5:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You're limited to whenever Yahoo updates their historical quotes. It's been
a long time since I used them regularly, but I thought they used to do it
by 6 PM ET. If they're now doing it later than that, nothing much you can
do -- unless you go with a different data provider. Possibly a paid one?
On Fri, Apr 26, 2013 at 5:04 PM, zoo62strat <w.baillie21@gmail.com > wrote:
> I am wondering if there is a way to get end of day info for stocks earlier
> than I presently do. Currently this function gives me updated daily data
> sometime after 9:00 pm. It would be nice to get it at market close, or even
> incomplete daily data before market close. Is this possible?
>
a long time since I used them regularly, but I thought they used to do it
by 6 PM ET. If they're now doing it later than that, nothing much you can
do -- unless you go with a different data provider. Possibly a paid one?
On Fri, Apr 26, 2013 at 5:04 PM, zoo62strat <w.baillie21@
> I am wondering if there is a way to get end of day info for stocks earlier
> than I presently do. Currently this function gives me updated daily data
> sometime after 9:00 pm. It would be nice to get it at market close, or even
> incomplete daily data before market close. Is this possible?
>
Fri Apr 26, 2013 5:28 pm (PDT) . Posted by:
"zoo62strat" zoo62strat
Randy, thanks for the help. Can you recommend a provider that would work for that stock data: Date,Open,High,
--- In smf_addin@yahoogrou
>
> You're limited to whenever Yahoo updates their historical quotes. It's been
> a long time since I used them regularly, but I thought they used to do it
> by 6 PM ET. If they're now doing it later than that, nothing much you can
> do -- unless you go with a different data provider. Possibly a paid one?
>
> On Fri, Apr 26, 2013 at 5:04 PM, zoo62strat <w.baillie21@
>
> > I am wondering if there is a way to get end of day info for stocks earlier
> > than I presently do. Currently this function gives me updated daily data
> > sometime after 9:00 pm. It would be nice to get it at market close, or even
> > incomplete daily data before market close. Is this possible?
> >
>
Fri Apr 26, 2013 5:50 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sorry, but I've not used any other services.
Yahoo's real-time quotes option should only affect how their quotes work
while the market is open. I don't think it has any affect on their
historical quotes offering.
On Fri, Apr 26, 2013 at 5:28 PM, zoo62strat <w.baillie21@gmail.com > wrote:
>
> Randy, thanks for the help. Can you recommend a provider that would work
> for that stock data: Date,Open,High,Low,Close,Volume? I am willing to pay
> to a certain degree. I see that Yahoo has a "real time quotes" service, but
> I'm not sure that this would work for what I am looking for...
>
Yahoo's real-time quotes option should only affect how their quotes work
while the market is open. I don't think it has any affect on their
historical quotes offering.
On Fri, Apr 26, 2013 at 5:28 PM, zoo62strat <w.baillie21@
>
> Randy, thanks for the help. Can you recommend a provider that would work
> for that stock data: Date,Open,High,
> to a certain degree. I see that Yahoo has a "real time quotes" service, but
> I'm not sure that this would work for what I am looking for...
>
Tidak ada komentar:
Posting Komentar