14 Messages
Digest #4267
Messages
Fri Dec 15, 2017 6:21 pm (PST) . Posted by:
antonvanas1989
Hi Randy,
Today I was trying to build a excel sheet for live up to date details from different websites (not yahoo).
I got some data with smfGetTagContent and RCHGetWebData. But I noticed that because of those functions use cache it is not really useful.
Tonight i was trying and searching for hours but I can not really get further.. What formulas I should use to receive live data or which can be refreshed often. (It will not be a lot so I am not really afraid of the loading time).
Today I was trying to build a excel sheet for live up to date details from different websites (not yahoo).
I got some data with smfGetTagContent and RCHGetWebData. But I noticed that because of those functions use cache it is not really useful.
Tonight i was trying and searching for hours but I can not really get further.. What formulas I should use to receive live data or which can be refreshed often. (It will not be a lot so I am not really afraid of the loading time).
Fri Dec 15, 2017 6:40 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
If IE is caching the data, there's nothing the add-in can do about it.
You'd have to change your Internet Options for that.
The add-in does save a copy of a web page before extracting data from it,
so that if you want 20 items from that web page it doesn't need to retrieve
the same web page 20 times. If you want the add-in to get a new copy of the
web page, you need to use the smfForceRecalculation macro. It purges all of
the saved copies of web pages, forcing the add-in to get a new copy. But,
again, if your Internet Options are set to caching of web pages, that "new
copy" will just be the cached copy of the web page from IE's Temporary
Internet Files folder. I have the smfForceRecalculation macro assigned to a
button on my Quick Access Toolbar.
The other thing you can do is to add a dummy parameter to your URL, since
the add-in is storing web pages based on the URL. However, note that the
add-in only has 1000 slots for storing web pages. Any request that need a
storage slot after 1000 are used will have "Error" returned.
There is also a "recalculate section" option on the SMF context menu. So
you could select a range for that option and it would do new web page
requests only for functions within that selected range (but only once per
URL).
On Fri, Dec 15, 2017 at 7:20 PM, antonvanas1989@
...
wrote:
>
> Today I was trying to build a excel sheet for live up to date details from
> different websites (not yahoo).
>
> I got some data with smfGetTagContent and RCHGetWebData. But I noticed
> that because of those functions use cache it is not really useful.
>
> Tonight i was trying and searching for hours but I can not really get
> further.. What formulas I should use to receive live data or which can be
> refreshed often. (It will not be a lot so I am not really afraid of the
> loading time).
>
You'd have to change your Internet Options for that.
The add-in does save a copy of a web page before extracting data from it,
so that if you want 20 items from that web page it doesn't need to retrieve
the same web page 20 times. If you want the add-in to get a new copy of the
web page, you need to use the smfForceRecalculati
the saved copies of web pages, forcing the add-in to get a new copy. But,
again, if your Internet Options are set to caching of web pages, that "new
copy" will just be the cached copy of the web page from IE's Temporary
Internet Files folder. I have the smfForceRecalculati
button on my Quick Access Toolbar.
The other thing you can do is to add a dummy parameter to your URL, since
the add-in is storing web pages based on the URL. However, note that the
add-in only has 1000 slots for storing web pages. Any request that need a
storage slot after 1000 are used will have "Error" returned.
There is also a "recalculate section" option on the SMF context menu. So
you could select a range for that option and it would do new web page
requests only for functions within that selected range (but only once per
URL).
On Fri, Dec 15, 2017 at 7:20 PM, antonvanas1989@
...
wrote:
>
> Today I was trying to build a excel sheet for live up to date details from
> different websites (not yahoo).
>
> I got some data with smfGetTagContent and RCHGetWebData. But I noticed
> that because of those functions use cache it is not really useful.
>
> Tonight i was trying and searching for hours but I can not really get
> further.. What formulas I should use to receive live data or which can be
> refreshed often. (It will not be a lot so I am not really afraid of the
> loading time).
>
Sat Dec 16, 2017 7:12 am (PST) . Posted by:
amt2100
I thought the auto recalc was worksheet specific. So you're saying if I open another or a blank worksheet, I can select it off and when I open the problematic sheet, it will not try to update? How do I change that setting?
Thanks again,
Jim
Thanks again,
Jim
Sat Dec 16, 2017 8:51 am (PST) . Posted by:
"Randy Harmelink" rharmelink
See:
https://support.microsoft.com/en-us/help/214395/how-excel-determines-the-current-mode-of-calculation
On Sat, Dec 16, 2017 at 8:12 AM, amt2100@
...
wrote:
>
> I thought the auto recalc was worksheet specific. So you're saying if I
> open another or a blank worksheet, I can select it off and when I open the
> problematic sheet, it will not try to update?
> How do I change that setting?
>
>
>
https://support.microsoft.com/en-us/help/214395/how-excel-determines-the-current-mode-of-calculation
On Sat, Dec 16, 2017 at 8:12 AM, amt2100@
...
wrote:
>
> I thought the auto recalc was worksheet specific. So you're saying if I
> open another or a blank worksheet, I can select it off and when I open the
> problematic sheet, it will not try to update?
> How do I change that setting?
>
>
>
Sat Dec 16, 2017 8:15 am (PST) . Posted by:
Yia_sou
I would like to get some ETF data from Vanguard's website: https://investor.vanguard.com/etf/list#/etf/asset-class/month-end-returns https://investor.vanguard.com/etf/list#/etf/asset-class/month-end-returns.
The data seems to populate in a way that the addin can not grab it or I am doing something wrong.
Has anyone been able to grab the info from this page?
Thank you.
The data seems to populate in a way that the addin can not grab it or I am doing something wrong.
Has anyone been able to grab the info from this page?
Thank you.
Sat Dec 16, 2017 9:11 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Sorry, but the web page is created dynamically, so it's not within the
source code of the web page to extract. As far as I can tell, the data is
retrieved via an API call that is not accessible outside of the context of
the web page.
On Sat, Dec 16, 2017 at 8:31 AM, yia_sou@
...
wrote:
>
> I would like to get some ETF data from Vanguard's website:
> https://investor.vanguard.com/etf/list#/etf/asset-class/month-end-returns.
>
>
> The data seems to populate in a way that the addin can not grab it or I am
> doing something wrong.
>
> Has anyone been able to grab the info from this page?
>
>
source code of the web page to extract. As far as I can tell, the data is
retrieved via an API call that is not accessible outside of the context of
the web page.
On Sat, Dec 16, 2017 at 8:31 AM, yia_sou@
...
wrote:
>
> I would like to get some ETF data from Vanguard's website:
> https://investor.vanguard.com/etf/list#/etf/asset-class/month-end-returns.
>
>
> The data seems to populate in a way that the addin can not grab it or I am
> doing something wrong.
>
> Has anyone been able to grab the info from this page?
>
>
Sat Dec 16, 2017 8:16 am (PST) . Posted by:
timsstockgroup
As a beginner using the SMF Add-In, I want to build a very simple worksheet to track a portfolio of stock, option, and fund prices, preferably real-time or delayed quotes. I have downloaded the 2.1.2017.11.30 build, followed the SMF Add-In installation instructions, and verified with =RCHGetElementNumber("Version") that it works to that point. From there I am stuck. Could someone help me find updated documentation or maybe a sample worksheet as a starting point for me to customize? It would be greatly appreciated!
Sat Dec 16, 2017 9:02 am (PST) . Posted by:
"Craig Passow" passow
There are lots of example spreadsheets at the SMF Add-In website (the
link is at the bottom of all messages that come through the Yahoo Group;
see below). You should be able to find something to get you started.
On 12/16/2017 10:01 AM, yahoogroups@mozom.org [smf_addin] wrote:
>
>
> As a beginner usingthe SMF Add-In, I want to build a very simple
> worksheet to track a portfolio of stock, option, and fund prices,
> preferably real-time or delayed quotes. I have downloaded
> the 2.1.2017.11.30 build, followed the SMF Add-In installation
> instructions, and verified with =RCHGetElementNumber("Version") that
> it works to that point. From there I am stuck. Could someone help me
> find updated documentation or maybe a sample worksheet as a starting
> point for me to customize? It would be greatly appreciated!
>
>
>
>
link is at the bottom of all messages that come through the Yahoo Group;
see below). You should be able to find something to get you started.
On 12/16/2017 10:01 AM, yahoogroups@mozom.org [smf_addin] wrote:
>
>
> As a beginner using
> worksheet to track a portfolio of stock, option, and fund prices,
> preferably real-time or delayed quotes. I have downloaded
> the 2.1.2017.
> instructions, and verified with =RCHGetElementN
> it works to that point. From there I am stuck. Could someone help me
> find updated documentation or maybe a sample worksheet as a starting
> point for me to customize? It would be greatly appreciated!
>
>
>
>
Sat Dec 16, 2017 9:14 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Check out the documentation and template on the add-in web site for the
smfGetYahooPortfolioView() function.
Quotes can be as simple as array-entering something like this over a 2-row
by 1-column range:
=smfGetYahooPortfolioView("MMM,IBM","15")
On Sat, Dec 16, 2017 at 9:01 AM, yahoogroups@
...
wrote:
> As a beginner using the SMF Add-In, I want to build a very simple
> worksheet to track a portfolio of stock, option, and fund prices,
> preferably real-time or delayed quotes. I have downloaded
> the 2.1.2017.11.30 build, followed the SMF Add-In installation
> instructions, and verified with =RCHGetElementNumber("Version") that it
> works to that point. From there I am stuck. Could someone help me find
> updated documentation or maybe a sample worksheet as a starting point for
> me to customize? It would be greatly appreciated!
>
>
>
smfGetYahooPortfoli
Quotes can be as simple as array-entering something like this over a 2-row
by 1-column range:
=smfGetYahooPortfol
On Sat, Dec 16, 2017 at 9:01 AM, yahoogroups@
...
wrote:
> As a beginner using the SMF Add-In, I want to build a very simple
> worksheet to track a portfolio of stock, option, and fund prices,
> preferably real-time or delayed quotes. I have downloaded
> the 2.1.2017.11.
> instructions, and verified with =RCHGetElementNumbe
> works to that point. From there I am stuck. Could someone help me find
> updated documentation or maybe a sample worksheet as a starting point for
> me to customize? It would be greatly appreciated!
>
>
>
Sat Dec 16, 2017 12:20 pm (PST) . Posted by:
climbermel
I have some spreadsheets that I have changed to use the smfGetYahooPortfolioView and it works when I put it together, but each time I open it, it displays #NAME in all the cells. If I select the array, click in the edit bar and do the cntl/shift/enter it refreshes fine. I've created a VBA script and added a "refresh" button to do just that, but I must have something set up wrong for this to be happening...
Any ideas on what may be wrong?
I just noticed, when I right click a cell, I no longer have the SMF menu that used to be there. Perhaps the add-in isn't installed properly? When I updated the add-in, I don't need to do anything to each individual spreadsheet do I?
Thanks,
Mel
Any ideas on what may be wrong?
I just noticed, when I right click a cell, I no longer have the SMF menu that used to be there. Perhaps the add-in isn't installed properly? When I updated the add-in, I don't need to do anything to each individual spreadsheet do I?
Thanks,
Mel
Sat Dec 16, 2017 1:37 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
When you start up EXCEL without opening a workbook that uses the add-in,
does the context menu exist? What do you get with:
=RCHGetElementNumber("Version")
If that works, what happens when you then open the workbook with the
smfGetYahooPortfolioView() function? Do you still have the context menu? If
the smfGetYahooPortfolioView() workbook doesn't work, what does the formula
look like?
The #NAME? problem is usually one of two things:
1. A location error (the location of the add-in within the add-in manger
doesn't match the location of the add-in that was in place when the
workbook was saved).
2. An issue caused by a MicroSoft security update in 2016. It prevents
EXCEL from loading the add-in when EXCEL starts up. For a workaround
suggested by Microsoft, until they supposedly fix the issue, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568
On Sat, Dec 16, 2017 at 1:20 PM, climbermel@
...
wrote:
> I have some spreadsheets that I have changed to use the
> smfGetYahooPortfolioView and it works when I put it together, but each time
> I open it, it displays #NAME in all the cells. If I select the array,
> click in the edit bar and do the cntl/shift/enter it refreshes fine. I've
> created a VBA script and added a "refresh" button to do just that, but I
> must have something set up wrong for this to be happening...
>
> Any ideas on what may be wrong?
>
> I just noticed, when I right click a cell, I no longer have the SMF menu
> that used to be there. Perhaps the add-in isn't installed properly? When
> I updated the add-in, I don't need to do anything to each individual
> spreadsheet do I?
>
>
>
does the context menu exist? What do you get with:
=RCHGetElementNumber("Version")
If that works, what happens when you then open the workbook with the
smfGetYahooPortfolioView() function? Do you still have the context menu? If
the smfGetYahooPortfolioView() workbook doesn't work, what does the formula
look like?
The #NAME? problem is usually one of two things:
1. A location error (the location of the add-in within the add-in manger
doesn't match the location of the add-in that was in place when the
workbook was saved).
2. An issue caused by a MicroSoft security update in 2016. It prevents
EXCEL from loading the add-in when EXCEL starts up. For a workaround
suggested by Microsoft, until they supposedly fix the issue, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568
On Sat, Dec 16, 2017 at 1:20 PM, climbermel@
...
wrote:
> I have some spreadsheets that I have changed to use the
> smfGetYahooPortfoli
> I open it, it displays #NAME in all the cells. If I select the array,
> click in the edit bar and do the cntl/shift/enter it refreshes fine. I've
> created a VBA script and added a "refresh" button to do just that, but I
> must have something set up wrong for this to be happening...
>
> Any ideas on what may be wrong?
>
> I just noticed, when I right click a cell, I no longer have the SMF menu
> that used to be there. Perhaps the add-in isn't installed properly? When
> I updated the add-in, I don't need to do anything to each individual
> spreadsheet do I?
>
>
>
Sat Dec 16, 2017 2:36 pm (PST) . Posted by:
climbermel
New wookbook blank worksheet, it has no context menu. I enter =RCHGetElementNumber("Version") into a random cell and I get an error #NAME?
I removed and re-added the add-in and now the context menu is there... odd that it would mess up when I haven't changed anything in a long time. Other than updating the files with the latest version a while back.
Now the formula works and returns: Stock Market Functions add-in, Version 2.1.2017.11.11 (C:\SMF Add-in; Windows (64-bit) NT 6.01; 14.0; ; ; 1)
I'm using Excel 2010 on Windows 7 64-bit
Mel
I removed and re-added the add-in and now the context menu is there... odd that it would mess up when I haven't changed anything in a long time. Other than updating the files with the latest version a while back.
Now the formula works and returns: Stock Market Functions add-in, Version 2.1.2017.11.
I'm using Excel 2010 on Windows 7 64-bit
Mel
Sat Dec 16, 2017 3:46 pm (PST) . Posted by:
climbermel
I close the spreadsheet, open a new one and it is back to not working...
I didn't think I did any updates recently so not sure why it would be the security update issue from a year ago.
Mel
I didn't think I did any updates recently so not sure why it would be the security update issue from a year ago.
Mel
Sat Dec 16, 2017 3:54 pm (PST) . Posted by:
climbermel
Well... no idea why this became an issue now, but I checked and unblocked the xla file and now it works! It's great that it now works, it just bothers me that I have no idea what happened.
Cheers,
Mel
Cheers,
Mel
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar