7 Messages
Digest #4339
Messages
Sat Jun 9, 2018 7:57 am (PDT) . Posted by:
lapub1
Hi Randy,
Is the smfGetYahooPortfolioView() function real time or delayed 15min? I was considering using the smfUpdateDownloadTable as I want to pull each data point individually and with a millisecond or two in between each pull. If I pull the data all at once, the large data requests seem to overload, and timeout the RTD.
Thanks!
Is the smfGetYahooPortfoli
Thanks!
Sat Jun 9, 2018 12:10 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
smfGetYahooPortfolioView() returns real-time quotes for stocks. Option
quotes are delayed. Not sure about other quotes, like futures and
currencies.
And a large array-entered function with lots of quotes and data items gets
returned very quickly.
On Fri, Jun 8, 2018 at 8:22 AM, lapub1@
...
wrote:
>
> Is the smfGetYahooPortfolioView() function real time or delayed 15min? I
> was considering using the smfUpdateDownloadTable as I want to pull each
> data point individually and with a millisecond or two in between each
> pull. If I pull the data all at once, the large data requests seem to
> overload, and timeout the RTD.
>
>
>
quotes are delayed. Not sure about other quotes, like futures and
currencies.
And a large array-entered function with lots of quotes and data items gets
returned very quickly.
On Fri, Jun 8, 2018 at 8:22 AM, lapub1@
...
wrote:
>
> Is the smfGetYahooPortfoli
> was considering using the smfUpdateDownloadTa
> data point individually and with a millisecond or two in between each
> pull. If I pull the data all at once, the large data requests seem to
> overload, and timeout the RTD.
>
>
>
Sat Jun 9, 2018 8:33 am (PDT) . Posted by:
"Jeff Fulkerson" jeff_fulkerson
First off, I wanted to say thank you. I have been using "smf addin' for years. Although I only use a small piece of its functionality, it has really been a time saver....until recently, that is. I have searched through other conversations, but haven't found any solutions to my problem. I apologize if this has been asked (and resolved) before.
I use the =smfPricesByDates function every day and it seems lately it has been a little hit-and-miss.
Here is an example of the formula that I use:
=smfPricesByDates(A5, DATE(2018,5,30))
Column "A" on my spreadsheet is a list of tickers. So it basically just pulls the price of the ticker in cell A5 for that particular date. Here is where I run into an issue. Sometimes the price gets "pulled in" without a problem, but more often than not, I get a "#N/A". It seems totally random. Let's say I have a spreadsheet with 300 tickers in it. Using the above formula, I'd say maybe 25-30% of the cells return actual prices. The remaining cells return "#N/A". What makes it more interesting, is if I have a cell with "#N/A" and change the date to 2018,5,31, sometimes I get a price in the cell.
I have also noticed that I am sometimes unable to get any cells in a spreadsheet to pull prices using that formula. This seems to happen most often at particular times of the day...usually around 11pm or 12am CST, which made me wonder about the "cookie and crumb" issue with Yahoo. I am not sure if that applies here or how to fix that. I will have to look further into that if you think that might be the problem.
I am using SMF version 2.1.2018.01.24 with Excel 2013 64-bit on Windows 7.
Any ideas of where I should start trying to fix this issue?
Thank you.
I use the =smfPricesByDates function every day and it seems lately it has been a little hit-and-miss.
Here is an example of the formula that I use:
=smfPricesByDates(
Column "A" on my spreadsheet is a list of tickers. So it basically just pulls the price of the ticker in cell A5 for that particular date. Here is where I run into an issue. Sometimes the price gets "pulled in" without a problem, but more often than not, I get a "#N/A"
I have also noticed that I am sometimes unable to get any cells in a spreadsheet to pull prices using that formula. This seems to happen most often at particular times of the day...usually around 11pm or 12am CST, which made me wonder about the "cookie and crumb" issue with Yahoo. I am not sure if that applies here or how to fix that. I will have to look further into that if you think that might be the problem.
I am using SMF version 2.1.2018.01.
Any ideas of where I should start trying to fix this issue?
Thank you.
Sat Jun 9, 2018 12:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The best solution to your problem is for me to understand why would you
need to get a historical quote for hundreds for stocks? It would only
change very rarely, after something like a dividend or split event.
The cookie/crumb issue is moot, because smfPricesByDates() calls
smfGetYahooHistory(), which extracts historical quotes from the web page
itself. It doesn't use the CSV file served up by the cookie/crumb process.
smfGetYahooHistory() is a relatively slow process. Well, at least a lot
slower than it was before Yahoo changed it last year. Historical quotes
requests used to happen in about a third to a half of a second. Now they
often take 2 seconds or more.
If you're using 300 functions, you must have a long recalculation time?
There may be ways to improve that, but it depends on what you're actually
trying to achieve.
I don't use smfPricesByDates() all that often, but I've not gotten #N/A
except where it made sense because the date didn't apply. My first
impression would be that you're not getting data returned because Yahoo is
getting overloaded with data requests?
*Hmm. Now that I mention that, it occurs to me that if you're hitting the
1000-web page limit of the add-in, #N/A might be what gets returned for any
request for a historical quotes web page above that 1000-page limit? How
many other SMF data requests are you doing at the same time, that use
unique web pages?*
On Sat, Jun 9, 2018 at 8:31 AM, Jeff Fulkerson jeff_fulkerson@
...
wrote:
>
> First off, I wanted to say thank you. I have been using "smf addin' for
> years. Although I only use a small piece of its functionality, it has
> really been a time saver....until recently, that is. I have searched
> through other conversations, but haven't found any solutions to my problem.
> I apologize if this has been asked (and resolved) before.
>
> I use the =smfPricesByDates function every day and it seems lately it has
> been a little hit-and-miss.
>
> Here is an example of the formula that I use:
>
> *=smfPricesByDates(A5, DATE(2018,5,30))*
>
> Column "A" on my spreadsheet is a list of tickers. So it basically just
> pulls the price of the ticker in cell A5 for that particular date. Here is
> where I run into an issue. Sometimes the price gets "pulled in" without a
> problem, but more often than not, I get a "*#N/A*". It seems totally
> random. Let's say I have a spreadsheet with 300 tickers in it. Using the
> above formula, I'd say maybe 25-30% of the cells return actual prices. The
> remaining cells return "*#N/A*". What makes it more interesting, is if I
> have a cell with "*#N/A*" and change the date to 2018,5,31, sometimes I
> get a price in the cell.
>
> I have also noticed that I am sometimes unable to get any cells in a
> spreadsheet to pull prices using that formula. This seems to happen most
> often at particular times of the day...usually around 11pm or 12am CST,
> which made me wonder about the "cookie and crumb" issue with Yahoo. I am
> not sure if that applies here or how to fix that. I will have to look
> further into that if you think that might be the problem.
>
> I am using SMF version 2.1.2018.01.24 with Excel 2013 64-bit on Windows 7.
>
> Any ideas of where I should start trying to fix this issue?
>
> Thank you.
>
need to get a historical quote for hundreds for stocks? It would only
change very rarely, after something like a dividend or split event.
The cookie/crumb issue is moot, because smfPricesByDates(
smfGetYahooHistory(
itself. It doesn't use the CSV file served up by the cookie/crumb process.
smfGetYahooHistory(
slower than it was before Yahoo changed it last year. Historical quotes
requests used to happen in about a third to a half of a second. Now they
often take 2 seconds or more.
If you're using 300 functions, you must have a long recalculation time?
There may be ways to improve that, but it depends on what you're actually
trying to achieve.
I don't use smfPricesByDates(
except where it made sense because the date didn't apply. My first
impression would be that you're not getting data returned because Yahoo is
getting overloaded with data requests?
*Hmm. Now that I mention that, it occurs to me that if you're hitting the
1000-web page limit of the add-in, #N/A might be what gets returned for any
request for a historical quotes web page above that 1000-page limit? How
many other SMF data requests are you doing at the same time, that use
unique web pages?*
On Sat, Jun 9, 2018 at 8:31 AM, Jeff Fulkerson jeff_fulkerson@
...
wrote:
>
> First off, I wanted to say thank you. I have been using "smf addin' for
> years. Although I only use a small piece of its functionality, it has
> really been a time saver....until recently, that is. I have searched
> through other conversations, but haven't found any solutions to my problem.
> I apologize if this has been asked (and resolved) before.
>
> I use the =smfPricesByDates function every day and it seems lately it has
> been a little hit-and-miss.
>
> Here is an example of the formula that I use:
>
> *=smfPricesByDates(
>
> Column "A" on my spreadsheet is a list of tickers. So it basically just
> pulls the price of the ticker in cell A5 for that particular date. Here is
> where I run into an issue. Sometimes the price gets "pulled in" without a
> problem, but more often than not, I get a "*#N/A*
> random. Let's say I have a spreadsheet with 300 tickers in it. Using the
> above formula, I'd say maybe 25-30% of the cells return actual prices. The
> remaining cells return "*#N/A*
> have a cell with "*#N/A*
> get a price in the cell.
>
> I have also noticed that I am sometimes unable to get any cells in a
> spreadsheet to pull prices using that formula. This seems to happen most
> often at particular times of the day...usually around 11pm or 12am CST,
> which made me wonder about the "cookie and crumb" issue with Yahoo. I am
> not sure if that applies here or how to fix that. I will have to look
> further into that if you think that might be the problem.
>
> I am using SMF version 2.1.2018.01.
>
> Any ideas of where I should start trying to fix this issue?
>
> Thank you.
>
Sat Jun 9, 2018 1:47 pm (PDT) . Posted by:
jeff_fulkerson
Thanks for you quick reply. I did not know about the 1000-page limit. That might be part of the problem. I use the smfPricesbyDates function as part of my backtesting for different strategies. I run scans nightly, copy the returned tickers into Excel and use the smfPricesbyDates to compare prices (day of the scan, 1 day later, and 7 days later).
What triggers that 1000-page limit? Is that per workbook? Per worksheet? Or is that some sort of daily limit no matter how many workbooks you have? The workbook am having the most problems with currently has 14 worksheets, all with 200-300 tickers in it. So if that 1000-page limit is per workbook, then I can see that might be my problem.
I do have another workbook that I use to monitor my portfolio. That workbook only has 8-10 cells using the smfPricesbyDates function, and I have found that it sometimes returns the #N/A error. I say sometimes, because it seems random. Sometimes it works, sometimes it doesn't. Last night (around midnight) it wasn't working; this morning (around 9 am) it was working fine. Which made me wonder if it was a time of day issue for some reason. But if that 1000-page limit is a daily limit, it could be that I had reached that limit last night which cause the #N/A error, but when I opened it this morning, I had not reached the limit yet so everything was fine.
And to answer your question, yes, the calculation time is pretty long. When I open the spreadsheet, it currently takes about 8 minutes to run the calculations after opening the workbook before I can even start using it. I tried turning off autocalculations and do it manually, but that became a bit of a pain so I went back to auto calculations.
Is there a way to only pull the historical prices into Excel one time and not have to keep pulling the same data down every time I open it? Because, like you said, it is historical data; it rarely changes. If there is a way to do that, that might solve this problem.
Thanks for your assistance. I appreciate it!
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
The best solution to your problem is for me to understand why would you need to get a historical quote for hundreds for stocks? It would only change very rarely, after something like a dividend or split event.
The cookie/crumb issue is moot, because smfPricesByDates() calls smfGetYahooHistory(), which extracts historical quotes from the web page itself. It doesn't use the CSV file served up by the cookie/crumb process.
smfGetYahooHistory() is a relatively slow process. Well, at least a lot slower than it was before Yahoo changed it last year. Historical quotes requests used to happen in about a third to a half of a second. Now they often take 2 seconds or more.
If you're using 300 functions, you must have a long recalculation time? There may be ways to improve that, but it depends on what you're actually trying to achieve.
I don't use smfPricesByDates() all that often, but I've not gotten #N/A except where it made sense because the date didn't apply. My first impression would be that you're not getting data returned because Yahoo is getting overloaded with data requests?
Hmm. Now that I mention that, it occurs to me that if you're hitting the 1000-web page limit of the add-in, #N/A might be what gets returned for any request for a historical quotes web page above that 1000-page limit? How many other SMF data requests are you doing at the same time, that use unique web pages?
On Sat, Jun 9, 2018 at 8:31 AM, Jeff Fulkerson jeff_fulkerson@ ....
wrote:
First off, I wanted to say thank you. I have been using "smf addin' for years. Although I only use a small piece of its functionality, it has really been a time saver....until recently, that is. I have searched through other conversations, but haven't found any solutions to my problem. I apologize if this has been asked (and resolved) before.
I use the =smfPricesByDates function every day and it seems lately it has been a little hit-and-miss.
Here is an example of the formula that I use:
=smfPricesByDates(A5, DATE(2018,5,30))
Column "A" on my spreadsheet is a list of tickers. So it basically just pulls the price of the ticker in cell A5 for that particular date. Here is where I run into an issue. Sometimes the price gets "pulled in" without a problem, but more often than not, I get a "#N/A". It seems totally random. Let's say I have a spreadsheet with 300 tickers in it. Using the above formula, I'd say maybe 25-30% of the cells return actual prices. The remaining cells return "#N/A". What makes it more interesting, is if I have a cell with "#N/A" and change the date to 2018,5,31, sometimes I get a price in the cell.
I have also noticed that I am sometimes unable to get any cells in a spreadsheet to pull prices using that formula. This seems to happen most often at particular times of the day...usually around 11pm or 12am CST, which made me wonder about the "cookie and crumb" issue with Yahoo. I am not sure if that applies here or how to fix that. I will have to look further into that if you think that might be the problem.
I am using SMF version 2.1.2018.01.24 with Excel 2013 64-bit on Windows 7.
Any ideas of where I should start trying to fix this issue?
Thank you.
What triggers that 1000-page limit? Is that per workbook? Per worksheet? Or is that some sort of daily limit no matter how many workbooks you have? The workbook am having the most problems with currently has 14 worksheets, all with 200-300 tickers in it. So if that 1000-page limit is per workbook, then I can see that might be my problem.
I do have another workbook that I use to monitor my portfolio. That workbook only has 8-10 cells using the smfPricesbyDates function, and I have found that it sometimes returns the #N/A error. I say sometimes, because it seems random. Sometimes it works, sometimes it doesn't. Last night (around midnight) it wasn't working; this morning (around 9 am) it was working fine. Which made me wonder if it was a time of day issue for some reason. But if that 1000-page limit is a daily limit, it could be that I had reached that limit last night which cause the #N/A error, but when I opened it this morning, I had not reached the limit yet so everything was fine.
And to answer your question, yes, the calculation time is pretty long. When I open the spreadsheet, it currently takes about 8 minutes to run the calculations after opening the workbook before I can even start using it. I tried turning off autocalculations and do it manually, but that became a bit of a pain so I went back to auto calculations.
Is there a way to only pull the historical prices into Excel one time and not have to keep pulling the same data down every time I open it? Because, like you said, it is historical data; it rarely changes. If there is a way to do that, that might solve this problem.
Thanks for your assistance. I appreciate it!
---In smf_addin@yahoogroups.com, <rharmelink@
The best solution to your problem is for me to understand why would you need to get a historical quote for hundreds for stocks? It would only change very rarely, after something like a dividend or split event.
The cookie/crumb issue is moot, because smfPricesByDates(
smfGetYahooHistory(
If you're using 300 functions, you must have a long recalculation time? There may be ways to improve that, but it depends on what you're actually trying to achieve.
I don't use smfPricesByDates(
Hmm. Now that I mention that, it occurs to me that if you're hitting the 1000-web page limit of the add-in, #N/A might be what gets returned for any request for a historical quotes web page above that 1000-page limit? How many other SMF data requests are you doing at the same time, that use unique web pages?
On Sat, Jun 9, 2018 at 8:31 AM, Jeff Fulkerson jeff_fulkerson@ ....
wrote:
First off, I wanted to say thank you. I have been using "smf addin' for years. Although I only use a small piece of its functionality, it has really been a time saver....until recently, that is. I have searched through other conversations, but haven't found any solutions to my problem. I apologize if this has been asked (and resolved) before.
I use the =smfPricesByDates function every day and it seems lately it has been a little hit-and-miss.
Here is an example of the formula that I use:
=smfPricesByDates(
Column "A" on my spreadsheet is a list of tickers. So it basically just pulls the price of the ticker in cell A5 for that particular date. Here is where I run into an issue. Sometimes the price gets "pulled in" without a problem, but more often than not, I get a "#N/A"
I have also noticed that I am sometimes unable to get any cells in a spreadsheet to pull prices using that formula. This seems to happen most often at particular times of the day...usually around 11pm or 12am CST, which made me wonder about the "cookie and crumb" issue with Yahoo. I am not sure if that applies here or how to fix that. I will have to look further into that if you think that might be the problem.
I am using SMF version 2.1.2018.01.
Any ideas of where I should start trying to fix this issue?
Thank you.
Sat Jun 9, 2018 3:57 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
See replies below. For information on the two macros I mention, check out
the Tips and FAQs page on the web site. I haven't yet created formal
documentation for either. Documentation always seems to be the last thing
done.
On Sat, Jun 9, 2018 at 1:47 PM, jeff_fulkerson@
...
wrote:
>
> Thanks for you quick reply. I did not know about the 1000-page limit. That
> might be part of the problem. I use the smfPricesbyDates function as part
> of my backtesting for different strategies. I run scans nightly, copy the
> returned tickers into Excel and use the smfPricesbyDates to compare prices
> (day of the scan, 1 day later, and 7 days later).
>
I hope you're getting all three items with a single array-entered
smfPricesByDates()? Otherwise, each call would get its own Yahoo
historical quotes web page. Using three individual formulas would basically
triple your recalculation time and triple the number of web page slots
being used.
> What triggers that 1000-page limit? Is that per workbook? Per worksheet?
> Or is that some sort of daily limit no matter how many workbooks you have?
> The workbook am having the most problems with currently has 14 worksheets,
> all with 200-300 tickers in it. So if that 1000-page limit is per workbook,
> then I can see that might be my problem.
>
It's actually per "session", over all workbooks and all worksheets used in
that session. What happens is that the add-in creates a VBA array of 1000
slots to store web pages. When an add-in function retrieves a web page, it
store it in one of those slots. THEN the data is extracted from that stored
copied of the web page. That's so if multiple data items are being
retrieved from a web page, the web page only needs to be retrieved once.
But once those 1000 slots are filled up, any further requests for a web
page error out. Closing a workbook doesn't free up any slots used by that
workbook.
So the VBA project is for EXCEL. Not per workbook. Not per worksheet. It
gets loaded when you start up EXCEL. So, one way to reset it would be to
exit EXCEL and start again. Another way to reset those 1000 slots is to run
the smfForceRecalculation macro -- it empty all 1000 slots. But that means
any function in an open workbook that needs to recalculate based on a web
page would need to have the web page retrieved again, and store in a slot,
so it could re-extract the data. It basically just "refreshes" the web
pages and recalculates everything.
However, if your open workbooks use over 1000 web pages, only the first
1000 web page retrievals will work.
Note that the add-in is designed for ad hoc usage, not bulk data retrieval.
I do have another workbook that I use to monitor my portfolio. That
> workbook only has 8-10 cells using the smfPricesbyDates function, and I
> have found that it sometimes returns the #N/A error. I say sometimes,
> because it seems random. Sometimes it works, sometimes it doesn't. Last
> night (around midnight) it wasn't working; this morning (around 9 am) it
> was working fine. Which made me wonder if it was a time of day issue for
> some reason. But if that 1000-page limit is a daily limit, it could be that
> I had reached that limit last night which cause the #N/A error, but when I
> opened it this morning, I had not reached the limit yet so everything was
> fine.
>
It would appear random, because EXCEL doesn't recalculate in the same
order all the time. And whichever functions happen to retrieve the first
1000 web pages would work correctly. They could even have been used up by
earlier workbooks.
I always have EXCEL open, with an empty workbook, so I can do quick things
that won't be saved. Much like a notepad. If I open up a workbook that uses
SMF functions, and then close it, I usually click my toolbar button to run
the smfForceRecalculation button. That empties all of those 1000 storage
slots, so I'm ready to go for the next SMF workbook I might open.
But, again, that won't help if an individual workbook needs over 1000 web
page retrievals. It will error out on all requests over 1000 web pages.
> And to answer your question, yes, the calculation time is pretty long.
> When I open the spreadsheet, it currently takes about 8 minutes to run the
> calculations after opening the workbook before I can even start using it. I
> tried turning off autocalculations and do it manually, but that became a
> bit of a pain so I went back to auto calculations.
>
> Is there a way to only pull the historical prices into Excel one time and
> not have to keep pulling the same data down every time I open it? Because,
> like you said, it is historical data; it rarely changes. If there is a way
> to do that, that might solve this problem.
>
Have you ever used the smfUpdateDownloadTable process? I use it quite a
bit for tabular data. It requires a little bit of setup (I always start a
new such workbook from the template, which is ready to go). What the
smfUpdateDownloadTable does if fill in a table, where rows are defined by
"ticker" symbols that fill in formulas, and the columns are defined by
text-format formulas that have placeholders for the "ticker" symbols. That
template:
http://ogres-crypt.com/SMF/Templates/smfUpdateDownloadTable-Sample.xls
There are two BIG advantages to using smfUpdateDownloadTable:
1. The table is filled with values. That means you don't need to ever
recalculate them if you don't need to. So, if you save the workbook and
re-open it, those same values will be in the table, without any need for a
long recalculation process to refresh the formulas.
2. YOU are in control of calculation. If you just run the macro, it goes
down row-by-row until it finds an empty cell in the "ticker" column, and it
goes rightward column-by-column until it finds and empty cell in the
"formula" row. However, you can select rows, select columns, or even a
range within the table, and then when you run the macro, it only updates
the values in the selected portion of the table.
One of my regular workbooks for this grabs next earning dates and next
ex-dividend dates. Since they only update once a quarter, I don't always
need to update the whole table. I can sort by the existing dates and just
update the few rows that appear to need updates.I always add a "Timestamp"
column so I now when I last updated the entire row.
That workbook looks something like this:
*Ticker* *TimeStamp*
*GuruFocus Next EarnDate*
*GuruFocus Ex-DividendDate*
*GuruFocus Ex-DividendAmount*
*Yahoo Ex-DividendDate*
*Best Ex-DividendDate*
*Future Ex-DividendDate* *Flag*
*MCD* 2018-05-29 10:15 2018-07-25 2018-02-28 $1.01 2017-06-01 2018-06-01
2018-06-01 ****
*TRV* 2018-05-14 07:57 2018-07-20 2018-06-07 $0.77 2017-06-07 2018-06-07
2018-06-07
*MO* 2018-05-29 10:15 2018-07-27 2018-06-14 $0.70 2017-06-13 2018-06-14
2018-06-14
The "Yahoo" and "Best" columns allow me to estimate future ex-dividend
dates that haven't yet been declared. The "Flag" column flags such
estimated ex-dividend dates. Note that the estimated ex-dividend date for
MCD, from May 29th, turned out to be the actual ex-dividend date.
The "formula definition" for that next earnings date column looks like
this:
IFERROR(DATEVALUE(smfStrExtr(smfGetTagContent("https://www.
gurufocus.com/stock/~~~~~","span",-1,"Next Earnings Date:"),":",")")),"--")
So when it computed it for the MCD row, it basically just calculated:
=IFERROR(DATEVALUE(smfStrExtr(smfGetTagContent("https://www.
gurufocus.com/stock/MCD","span";,-1,"Next Earnings Date:"),":",")")),"--")
.... because "~~~~~" is the place holder for where the "ticker symbol" is to
be substituted.
The "Flag" formula looks like this:
IF(OR("~~~1~~~"="~~~5~~~","~~~1~~~"="--"),"","****")
The "~~~1~~~" and "~~~5~~~" says to grab the values in the 1st and 5th
previous columns of that row. So "~~~1~~~" would pick up the "Best
Ex-dividend date" and "~~~5~~~" would pick up the "GuruFocus ex-dividend
date", and create a flag if they are not the same.
If I want to use that earnings date in another of my workbooks, I don't
need to retrieve it from the Internet. I can just retrieve it from this
workbook, with:
=INDEX('SMF-Template-Ex-Dividend-Dates.xls'!eGFEarnDate,MATCH("MCD",'[SMF-
Template-Ex-Dividend-Dates.xls]ExDivDates'!eTickers,0))
....where "eGFEarnDate" and "eTickers"; are defined names of that workbook
for the applicable columns. I use the "e" prefix on defined names to
indicate the are primarily for external lookup usage.
I use the smfUpdateDownloadTable a lot for processes that don't always need
current data, or for when I want to selectively control when updates are
done. Unfortunately, it doesn't return multiple items from an array-entered
formula. I haven't spent the time to figure out a method for that yet.
P.S. I quickly threw together a sample using your "Close", "Close+1", and
"Close+7". It's attached. Note that I used a defined name, "cTestDate" (the
"c" prefix means it is a column designation for smfUpdateDownloadTable), to
refer back to a data column next to the "Ticker" column. It just creates
the "~~~n"~~~" designation for each column referback. That's because a
hard-coded "~~~2~~~" can be a problem if columns of the table are inserted
or deleted. By using a defined name like that, such deletions and
insertions have the column reference automatically adjusted.
Unfortunately, it does have to do each smfPricesByDates() function
individually, so it would still be using three storage slots for the web
pages. But they should never have to be retrieved again...although you
could if you wanted to.
P.P.S. I have both smfForceRecalculation and smfUpdateDownloadTable buttons
on my toolbar, but they should also be available on the context menu (via a
right mouse click on a worksheet cell).
the Tips and FAQs page on the web site. I haven't yet created formal
documentation for either. Documentation always seems to be the last thing
done.
On Sat, Jun 9, 2018 at 1:47 PM, jeff_fulkerson@
...
wrote:
>
> Thanks for you quick reply. I did not know about the 1000-page limit. That
> might be part of the problem. I use the smfPricesbyDates function as part
> of my backtesting for different strategies. I run scans nightly, copy the
> returned tickers into Excel and use the smfPricesbyDates to compare prices
> (day of the scan, 1 day later, and 7 days later).
>
I hope you're getting all three items with a single array-entered
smfPricesByDates()? Otherwise, each call would get its own Yahoo
historical quotes web page. Using three individual formulas would basically
triple your recalculation time and triple the number of web page slots
being used.
> What triggers that 1000-page limit? Is that per workbook? Per worksheet?
> Or is that some sort of daily limit no matter how many workbooks you have?
> The workbook am having the most problems with currently has 14 worksheets,
> all with 200-300 tickers in it. So if that 1000-page limit is per workbook,
> then I can see that might be my problem.
>
It's actually per "session", over all workbooks and all worksheets used in
that session. What happens is that the add-in creates a VBA array of 1000
slots to store web pages. When an add-in function retrieves a web page, it
store it in one of those slots. THEN the data is extracted from that stored
copied of the web page. That's so if multiple data items are being
retrieved from a web page, the web page only needs to be retrieved once.
But once those 1000 slots are filled up, any further requests for a web
page error out. Closing a workbook doesn't free up any slots used by that
workbook.
So the VBA project is for EXCEL. Not per workbook. Not per worksheet. It
gets loaded when you start up EXCEL. So, one way to reset it would be to
exit EXCEL and start again. Another way to reset those 1000 slots is to run
the smfForceRecalculation macro -- it empty all 1000 slots. But that means
any function in an open workbook that needs to recalculate based on a web
page would need to have the web page retrieved again, and store in a slot,
so it could re-extract the data. It basically just "refreshes" the web
pages and recalculates everything.
However, if your open workbooks use over 1000 web pages, only the first
1000 web page retrievals will work.
Note that the add-in is designed for ad hoc usage, not bulk data retrieval.
I do have another workbook that I use to monitor my portfolio. That
> workbook only has 8-10 cells using the smfPricesbyDates function, and I
> have found that it sometimes returns the #N/A error. I say sometimes,
> because it seems random. Sometimes it works, sometimes it doesn't. Last
> night (around midnight) it wasn't working; this morning (around 9 am) it
> was working fine. Which made me wonder if it was a time of day issue for
> some reason. But if that 1000-page limit is a daily limit, it could be that
> I had reached that limit last night which cause the #N/A error, but when I
> opened it this morning, I had not reached the limit yet so everything was
> fine.
>
It would appear random, because EXCEL doesn't recalculate in the same
order all the time. And whichever functions happen to retrieve the first
1000 web pages would work correctly. They could even have been used up by
earlier workbooks.
I always have EXCEL open, with an empty workbook, so I can do quick things
that won't be saved. Much like a notepad. If I open up a workbook that uses
SMF functions, and then close it, I usually click my toolbar button to run
the smfForceRecalculation button. That empties all of those 1000 storage
slots, so I'm ready to go for the next SMF workbook I might open.
But, again, that won't help if an individual workbook needs over 1000 web
page retrievals. It will error out on all requests over 1000 web pages.
> And to answer your question, yes, the calculation time is pretty long.
> When I open the spreadsheet, it currently takes about 8 minutes to run the
> calculations after opening the workbook before I can even start using it. I
> tried turning off autocalculations and do it manually, but that became a
> bit of a pain so I went back to auto calculations.
>
> Is there a way to only pull the historical prices into Excel one time and
> not have to keep pulling the same data down every time I open it? Because,
> like you said, it is historical data; it rarely changes. If there is a way
> to do that, that might solve this problem.
>
Have you ever used the smfUpdateDownloadTable process? I use it quite a
bit for tabular data. It requires a little bit of setup (I always start a
new such workbook from the template, which is ready to go). What the
smfUpdateDownloadTable does if fill in a table, where rows are defined by
"ticker" symbols that fill in formulas, and the columns are defined by
text-format formulas that have placeholders for the "ticker" symbols. That
template:
http://ogres-crypt.com/SMF/Templates/smfUpdateDownloadTable-Sample.xls
There are two BIG advantages to using smfUpdateDownloadTable:
1. The table is filled with values. That means you don't need to ever
recalculate them if you don't need to. So, if you save the workbook and
re-open it, those same values will be in the table, without any need for a
long recalculation process to refresh the formulas.
2. YOU are in control of calculation. If you just run the macro, it goes
down row-by-row until it finds an empty cell in the "ticker" column, and it
goes rightward column-by-column until it finds and empty cell in the
"formula" row. However, you can select rows, select columns, or even a
range within the table, and then when you run the macro, it only updates
the values in the selected portion of the table.
One of my regular workbooks for this grabs next earning dates and next
ex-dividend dates. Since they only update once a quarter, I don't always
need to update the whole table. I can sort by the existing dates and just
update the few rows that appear to need updates.I always add a "Timestamp"
column so I now when I last updated the entire row.
That workbook looks something like this:
*Ticker* *TimeStamp*
*GuruFocus Next EarnDate*
*GuruFocus Ex-DividendDate*
*GuruFocus Ex-DividendAmount*
*Yahoo Ex-DividendDate*
*Best Ex-DividendDate*
*Future Ex-DividendDate* *Flag*
*MCD* 2018-05-29 10:15 2018-07-25 2018-02-28 $1.01 2017-06-01 2018-06-01
2018-06-01 ****
*TRV* 2018-05-14 07:57 2018-07-20 2018-06-07 $0.77 2017-06-07 2018-06-07
2018-06-07
*MO* 2018-05-29 10:15 2018-07-27 2018-06-14 $0.70 2017-06-13 2018-06-14
2018-06-14
The "Yahoo" and "Best" columns allow me to estimate future ex-dividend
dates that haven't yet been declared. The "Flag" column flags such
estimated ex-dividend dates. Note that the estimated ex-dividend date for
MCD, from May 29th, turned out to be the actual ex-dividend date.
The "formula definition" for that next earnings date column looks like
this:
IFERROR(DATEVALUE(smfStrExtr(smfGetTagContent("https://www.
gurufocus.com/stock/~~~~~","span",-1,"Next Earnings Date:"),":",")")),"--")
So when it computed it for the MCD row, it basically just calculated:
=IFERROR(DATEVALUE(smfStrExtr(smfGetTagContent("https://www.
gurufocus.com/
.... because "~~~~~" is the place holder for where the "ticker symbol" is to
be substituted.
The "Flag" formula looks like this:
IF(OR("
The "~~~1~~
previous columns of that row. So "~~~1~~
Ex-dividend date" and "~~~5~~
date", and create a flag if they are not the same.
If I want to use that earnings date in another of my workbooks, I don't
need to retrieve it from the Internet. I can just retrieve it from this
workbook, with:
=INDEX('
Template-Ex-
....where "eGFEarnDate&q
for the applicable columns. I use the "e" prefix on defined names to
indicate the are primarily for external lookup usage.
I use the smfUpdateDownloadTa
current data, or for when I want to selectively control when updates are
done. Unfortunately, it doesn't return multiple items from an array-entered
formula. I haven't spent the time to figure out a method for that yet.
P.S. I quickly threw together a sample using your "Close"
"Close+
"c" prefix means it is a column designation for smfUpdateDownloadTa
refer back to a data column next to the "Ticker" column. It just creates
the "~~~n"
hard-coded "~~~2~~
or deleted. By using a defined name like that, such deletions and
insertions have the column reference automatically adjusted.
Unfortunately, it does have to do each smfPricesByDates(
individually, so it would still be using three storage slots for the web
pages. But they should never have to be retrieved again...although you
could if you wanted to.
P.P.S. I have both smfForceRecalculati
on my toolbar, but they should also be available on the context menu (via a
right mouse click on a worksheet cell).
Sat Jun 9, 2018 4:15 pm (PDT) . Posted by:
jeff_fulkerson
This is fantastic info. Thank you so much! I will start playing around with some of the things you mentioned later tonight. I will let you know if I run into any issues or have further questions.
Thanks again!
Thanks again!
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar