11 Messages
Digest #4280
Messages
Sat Dec 30, 2017 7:05 pm (PST) . Posted by:
jhfrazer@ymail.com
Hi Randy,
Understood. I am attaching a portion of a workbook that I have been using since the dinosaurs ruled the earth. The spreadsheet performs a number of tasks. Some of them in a rather clumsy fashion. It also takes forever to recalculate even with 16 cores.
Any thoughts you might offer on how best to implement the GetYahooQuotesPortfilioView function with type of a layout would be appreciated.
Any thoughts on streamlining the workbook as a whole would of course be appreciated as well.
Cheers and THANK YOU...
Understood. I am attaching a portion of a workbook that I have been using since the dinosaurs ruled the earth. The spreadsheet performs a number of tasks. Some of them in a rather clumsy fashion. It also takes forever to recalculate even with 16 cores.
Any thoughts you might offer on how best to implement the GetYahooQuotesPortf
Any thoughts on streamlining the workbook as a whole would of course be appreciated as well.
Cheers and THANK YOU...
Sat Dec 30, 2017 7:50 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
First think I'd do is get rid of all of the NOW() references. That makes
everything that uses it, directly or indirectly, volatile -- which means it
will recalculate ANY time the ANYTHING in the workbook changes.
I'd also change it so that all of your data retrieval is done in a
worksheet (or more) that are separate from your presentation of the data.
And organize it so that you do the least amount of data retrieval.
My log file indicated 341 requests to the Internet for data. It took over 2
minutes just to retrieve those web pages. That actually seems fast to me. :(
-- 90 to MorningStar, some to the "Print Report", which may already be
unreliable and gone in the near future?
-- 4 to Barchart, replaceable by a single smfGetBarchartPortfolioView()?
-- 6 to Google, which should be replaced, as the Google pages may disappear
at any time
-- 7 to IBD
-- 4 to Reuters
-- 34 to Yahoo history, some just to get a current quote?
-- 37 to Yahoo Key Statistics page
-- 45 to Yahoo JSON file modules
-- 78 to Yahoo current quotes
-- 8 to Zacks
There seemed to be a lot of overlap on sectors, industries, company names?
I'd check to see what smfGetYahooPortfolioView() and
smfGetBarchartPortfolioView() can replace for you. If those two function
invocations can replace hundreds of the above requests, your recalc time
should speed up significantly.
Also, if you have things that change infrequently, such as the sectors,
industries, and company names, or quarterly data items, you might want to
put them on separate worksheets using smfUpdateDownloadTable to grab the
data on demand. That macro put values into the workbook, so you don't need
to recalculate all of their values each time you open the workbook. But you
can always go to the source worksheet and either update everything, or
specific pieces.
On Sat, Dec 30, 2017 at 7:55 PM, jhfrazer@ymail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Understood. I am attaching a portion of a workbook that I have been using
> since the dinosaurs ruled the earth. The spreadsheet performs a number of
> tasks. Some of them in a rather clumsy fashion. It also takes forever to
> recalculate even with 16 cores.
>
> Any thoughts you might offer on how best to implement the
> GetYahooQuotesPortfilioView function with type of a layout would be
> appreciated.
>
> Any thoughts on streamlining the workbook as a whole would of course be
> appreciated as well.
>
>
everything that uses it, directly or indirectly, volatile -- which means it
will recalculate ANY time the ANYTHING in the workbook changes.
I'd also change it so that all of your data retrieval is done in a
worksheet (or more) that are separate from your presentation of the data.
And organize it so that you do the least amount of data retrieval.
My log file indicated 341 requests to the Internet for data. It took over 2
minutes just to retrieve those web pages. That actually seems fast to me. :(
-- 90 to MorningStar, some to the "Print Report", which may already be
unreliable and gone in the near future?
-- 4 to Barchart, replaceable by a single smfGetBarchartPortfolioView()?
-- 6 to Google, which should be replaced, as the Google pages may disappear
at any time
-- 7 to IBD
-- 4 to Reuters
-- 34 to Yahoo history, some just to get a current quote?
-- 37 to Yahoo Key Statistics page
-- 45 to Yahoo JSON file modules
-- 78 to Yahoo current quotes
-- 8 to Zacks
There seemed to be a lot of overlap on sectors, industries, company names?
I'd check to see what smfGetYahooPortfolioView() and
smfGetBarchartPortfolioView() can replace for you. If those two function
invocations can replace hundreds of the above requests, your recalc time
should speed up significantly.
Also, if you have things that change infrequently, such as the sectors,
industries, and company names, or quarterly data items, you might want to
put them on separate worksheets using smfUpdateDownloadTable to grab the
data on demand. That macro put values into the workbook, so you don't need
to recalculate all of their values each time you open the workbook. But you
can always go to the source worksheet and either update everything, or
specific pieces.
On Sat, Dec 30, 2017 at 7:55 PM, jhfrazer@ymail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Understood. I am attaching a portion of a workbook that I have been using
> since the dinosaurs ruled the earth. The spreadsheet performs a number of
> tasks. Some of them in a rather clumsy fashion. It also takes forever to
> recalculate even with 16 cores.
>
> Any thoughts you might offer on how best to implement the
> GetYahooQuotesPortf
> appreciated.
>
> Any thoughts on streamlining the workbook as a whole would of course be
> appreciated as well.
>
>
Sun Dec 31, 2017 7:22 am (PST) . Posted by:
jhfrazer@ymail.com
Thank you for the suggestions. They are greatly appreciated. I will try and deal with them in "chunks". I will chat back with additional questions as I plow forward.
Happy New Years
John
Happy New Years
John
Sat Dec 30, 2017 7:57 pm (PST) . Posted by:
"Ian Rogers" irianr1943
Hi Randy
Thanks for your reply. I had the feeling that it was something to do with
our time zone but I haven't got the knowledge to follow it up.
Yes you are right the Australian Market opens at 10:00am
Cheers
Ian Rogers
Thanks for your reply. I had the feeling that it was something to do with
our time zone but I haven't got the knowledge to follow it up.
Yes you are right the Australian Market opens at 10:00am
Cheers
Ian Rogers
Sat Dec 30, 2017 10:08 pm (PST) . Posted by:
navelhunt
Hi Randy,
Trying to find the best and quickest way to get the https://coinmarketcap.com/all/views/all/ https://coinmarketcap.com/all/views/all/ page into excel.. Page is with HTML tags so the smfgettagcontent function works. However, to use this for each cell in excel referring to the same page still slows down the excel a lot.
Given all the info is on a single page, is there a faster way to get the info down into excel - ie which function can i use?
Thanks!
Trying to find the best and quickest way to get the https://coinmarketcap.com/all/views/all/ https://coinmarketcap.com/all/views/all/ page into excel.. Page is with HTML tags so the smfgettagcontent function works. However, to use this for each cell in excel referring to the same page still slows down the excel a lot.
Given all the info is on a single page, is there a faster way to get the info down into excel - ie which function can i use?
Thanks!
Sat Dec 30, 2017 10:24 pm (PST) . Posted by:
navelhunt
Think i have found the solution - RCHgetHTMLtable. Thanks anyways. I hope there will be a solution soon for yahoo finance information / smfgetyahooquotes.
Sat Dec 30, 2017 10:30 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Was just writing this when your follow-up message arrived...try
array-entering this over a range:
=RCHGetHTMLTable("https://coinmarketcap.com/all/views/all/",">Circulating")
On Sat, Dec 30, 2017 at 11:08 PM, navelhunt@
...
wrote:
>
> Trying to find the best and quickest way to get the
> https://coinmarketcap.com/all/views/all/ page into excel. Page is with
> HTML tags so the smfgettagcontent function works. However, to use this for
> each cell in excel referring to the same page still slows down the excel a
> lot.
>
> Given all the info is on a single page, is there a faster way to get the
> info down into excel - ie which function can i use?
>
>
>
>
array-entering this over a range:
=RCHGetHTMLTable("https://coinmarketcap.com/all/views/all/",">Circulating")
On Sat, Dec 30, 2017 at 11:08 PM, navelhunt@
...
wrote:
>
> Trying to find the best and quickest way to get the
> https://coinmarketcap.com/all/views/all/ page into excel. Page is with
> HTML tags so the smfgettagcontent function works. However, to use this for
> each cell in excel referring to the same page still slows down the excel a
> lot.
>
> Given all the info is on a single page, is there a faster way to get the
> info down into excel - ie which function can i use?
>
>
>
>
Sat Dec 30, 2017 10:31 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The solution to RCHGetYahooQuotes() is to use the new
smfGetYahooPortfolioView().
On Sat, Dec 30, 2017 at 11:24 PM, navelhunt@
...
wrote:
>
> Think i have found the solution - RCHgetHTMLtable. Thanks anyways. I hope
> there will be a solution soon for yahoo finance information /
> smfgetyahooquotes.
>
>
>
smfGetYahooPortfoli
On Sat, Dec 30, 2017 at 11:24 PM, navelhunt@
...
wrote:
>
> Think i have found the solution - RCHgetHTMLtable. Thanks anyways. I hope
> there will be a solution soon for yahoo finance information /
> smfgetyahooquotes.
>
>
>
Sun Dec 31, 2017 9:02 am (PST) . Posted by:
jhfrazer@ymail.com
Randy,
Given you have had more experience than any of us...which do you prefer? Is one faster than the other? Does one source redesign their page more than another thereby likely breaking your function?
Happy New Year!!!
Given you have had more experience than any of us...which do you prefer? Is one faster than the other? Does one source redesign their page more than another thereby likely breaking your function?
Happy New Year!!!
Sun Dec 31, 2017 10:12 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I really did the Barchart because of all of the technical analysis items it
had. Also, so there was an another alternative. Both use JSON files.
I primarily use smfGetYahooPortfolioView(), because I usually need option
quotes.
On Sun, Dec 31, 2017 at 10:01 AM, jhfrazer@
...
wrote:
>
> Given you have had more experience than any of us...which do you prefer?
> Is one faster than the other? Does one source redesign their page more
> than another thereby likely breaking your function?
>
>
had. Also, so there was an another alternative. Both use JSON files.
I primarily use smfGetYahooPortfoli
quotes.
On Sun, Dec 31, 2017 at 10:01 AM, jhfrazer@
...
wrote:
>
> Given you have had more experience than any of us...which do you prefer?
> Is one faster than the other? Does one source redesign their page more
> than another thereby likely breaking your function?
>
>
Sun Dec 31, 2017 2:06 pm (PST) . Posted by:
e_bartsch
Thanks, Randy. That worked like a charm.
And, appreciate the pointer on the upgrade in smfStrExtr. That is handy!
Eric
And, appreciate the pointer on the upgrade in smfStrExtr. That is handy!
Eric
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar