4 Messages
Digest #3458
Messages
Thu Jul 16, 2015 1:57 pm (PDT) . Posted by:
BEYMELAMED
Andy,
I just started using your Excel plugin and am having a hard time.
I followed the instructions and created a file then installed the plugin and saw the version number appear.
I also downloaded various samples but am getting #NAME? everywhere.
Looking at the cells with #NAME error, I realize that the reference to the .xla is wrong.
Using find and replace does not help either (also does not help when I remove the plugin, replace then add the plugin again.)
For example:
My install folder is:
L:\SMF\ (this is where the .xla file is.)
I downloaded some sample files to L:\SMF\Docs
I open file L:\SMF\Docs\RCHGetTableCell-Template-MSN-Financial-Statements.xls.
In (for example) cell C8 I see:
='L:\SMF Add-in\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol= http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker, 1,"Period Length")
The cell itself displays #NAME?
I expected it to be:
='L:\SMF\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol= http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker, 1,"Period Length")
I tried:
1. 1. Find / Replace (this results in disaster replacement that I am not sure how it comes about and of course does not work.)
2. 2. Created a folder l:\SMF Add-in and placed the .xla there – No improvement
3. 3. Restarted the machine (Windows 7 pro 64 bits, Office 2007) and tried (manual) replacements in cells – now I got 'Error' displayed.
This is happening in most files.
There was only one file where the find / replace took hold and resolved the issue (Yahoo-History-Multiple-ROC-Timeframes.xls)
Sometimes, in some files the #NAME! Gets replaced by #VALUE!
Having created a folder C:\SMF on another machine and tried some of the above, resolved the issue in some, but not all, spreadsheets.
I really want to use your great project,
Thanks,
Avraham Melamed
I just started using your Excel plugin and am having a hard time.
I followed the instructions and created a file then installed the plugin and saw the version number appear.
I also downloaded various samples but am getting #NAME? everywhere.
Looking at the cells with #NAME error, I realize that the reference to the .xla is wrong.
Using find and replace does not help either (also does not help when I remove the plugin, replace then add the plugin again.)
For example:
My install folder is:
L:\SMF\ (this is where the .xla file is.)
I downloaded some sample files to L:\SMF\Docs
I open file L:\SMF\Docs\RCHGetTableCell-Template-MSN-Financial-Statements.xls.
In (for example) cell C8 I see:
='L:\SMF Add-in\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol= http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker, 1,"Period Length")
The cell itself displays #NAME?
I expected it to be:
='L:\SMF\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol= http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker, 1,"Period Length"
I tried:
1. 1. Find / Replace (this results in disaster replacement that I am not sure how it comes about and of course does not work.)
2. 2. Created a folder l:\SMF Add-in and placed the .xla there – No improvement
3. 3. Restarted the machine (Windows 7 pro 64 bits, Office 2007) and tried (manual) replacements in cells – now I got 'Error' displayed.
This is happening in most files.
There was only one file where the find / replace took hold and resolved the issue (Yahoo-History-
Sometimes, in some files the #NAME! Gets replaced by #VALUE!
Having created a folder C:\SMF on another machine and tried some of the above, resolved the issue in some, but not all, spreadsheets.
I really want to use your great project,
Thanks,
Avraham Melamed
Thu Jul 16, 2015 4:01 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The location errors (i.e. unresolved links and #NAME?) are something that
have to be lived with when sharing workbooks, since not everyone will have
the add-in the same location. For me, the smfFixLinks macro has only failed
when the worksheets are protected. But once I turn off the protection,
everything converts fine. Protection may be the reason the "Find and
Replace all" fail as well? I have the smfFixLinks macro saved to a button
on my toolbar, so it's very easy for me to run when I open a workbook that
has the unresolved links.
I know some people in the past have had issues with the add-in being
installed on a network drive. EXCEL seems to handle its usage differently.
But that may vary by which version of EXCEL and/or operating system you're
using.
One other useful macro is smfForceRecalculation. That's because the add-in
saves a copy of a web page before extracting data from the web page. So
just doing a normal recalculation just does and extract from the same saved
copy of the web page. But smfForceRecalculation purges the saved copy of a
web page and gets a fresh copy from the Internet. For some extractions,
where data may be changing constantly during the day, that can be an
essential macro to have on a button on your tool bar.
Also see comments below...
On Thu, Jul 16, 2015 at 1:57 PM, beymelamed@... wrote:
>
> Andy,
>
> I just started using your Excel plugin and am having a hard time.
>
> I followed the instructions and created a file then installed the plugin
> and saw the version number appear.
>
> I also downloaded various samples but am getting #NAME? everywhere.
>
> Looking at the cells with #NAME error, I realize that the reference to the
> .xla is wrong.
>
> Using find and replace does not help either (also does not help when I
> remove the plugin, replace then add the plugin again.)
>
>
>
> For example:
>
> My install folder is:
>
> L:\SMF\ (this is where the .xla file is.)
>
> I downloaded some sample files to L:\SMF\Docs
>
>
>
> I open file
> L:\SMF\Docs\RCHGetTableCell-Template-MSN-Financial-Statements.xls.
>
> In (for example) cell C8 I see:
>
> ='L:\*SMF Add-in*\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker,
> 1,"Period Length")
>
> The cell itself displays #NAME?
>
> I expected it to be:
>
> ='L:\*SMF*\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker,
> 1,"Period Length")
>
There should be NOTHING in front of the RCHGetTableCell() function. The
reason it has "SMF Add-in" as the top path is because that's where the
add-in was located when I saved the file. If it had your path, EXCEL would
have stripped off the entire link, because it was able to resolve it.
The L: drive identifier is part of the network drive issue I mentioned.
EXCEL/MicroSoft must handle the drive location differently when it opens
the file. Again, maybe it varies depending on version of EXCEL and O/S.
In any case, after making sure the worksheet isn't protected, doing and
"Find and Replace All" of:
'L:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
...and changing it to be nothing, should have fixed everything. That's all
the smfFixLinks macro does, although it uses a wildcard specification that
doesn't care about drive or path:
'*\RCH_Stock_Market_Functions.xla'!
>
>
> I tried:
>
> 1. 1. Find / Replace (this results in disaster replacement that I
> am not sure how it comes about and of course does not work.)
>
This doesn't make sense to me? Here, it won't even do the replacement if
the formula would be invalid after the replacement is performed.
> 2. 2. Created a folder l:\SMF Add-in and placed the .xla there – No
> improvement
>
Yes. That shouldn't have resolved anything, since the file being opened had
been saved with the add-in on the C: drive.
> 3. 3. Restarted the machine (Windows 7 pro 64 bits, Office 2007)
> and tried (manual) replacements in cells – now I got 'Error' displayed.
>
It may be working perfectly then. As of their website update last year, MSN
is obsolete as a data source. They removed 95% of their data items late
last year.
In any case, for 99.9% of SMF issues, it should be sufficient to simply
exit and restart EXCEL. That's because the add-in is only loaded once -- at
EXCEL startup. And a restart of EXCEL resets all of the VBA environment.
Hmmm. Looks like MSN has added financial statements back out there. I may
update those templates.
Just, as an example, to get the first column of total revenue from the
annal income statement:
=smfConvertData(smfGetTagContent("
http://www.msn.com/en-us/money/stockdetails/financials/income_statement/Annual/fi-MMM","p",1,">Total
Revenue"))
...or, if you're using the beta version of the add-in from the "Works in
Progress" folder, you could drop the smfConvertData() function and use the
pConv parameter of the smfGetTagContent() function:
=smfGetTagContent("
http://www.msn.com/en-us/money/stockdetails/financials/income_statement/Annual/fi-MMM","p",1,">Total
Revenue",,,,1)
> This is happening in most files.
>
> There was only one file where the find / replace took hold and resolved
> the issue (Yahoo-History-Multiple-ROC-Timeframes.xls)
>
> Sometimes, in some files the #NAME! Gets replaced by #VALUE!
>
Without specifics, hard to say why. If the SMF function gets an "Error"
value returned, which is then used in a calculation, that would end up as a
#VALUE! displayed error.
> Having created a folder C:\SMF on another machine and tried some of the
> above, resolved the issue in some, but not all, spreadsheets.
>
I really want to use your great project,
>
Sounds like you're just having trouble using saved workbooks, which is the
resolved links issue from EXCEL.
If you create your own formulas in a new workbook, are you having issues?
*Once you've fixed the unresolved links and save the workbook, you
shouldn't have unresolved links on that workbook again (unless you change
the location of the add-in). That's because when YOU save the file, it
saves a hard-coded link to the current location of the add-in on your
system. So when you open that workbook, it will find the add-in in that
location and resolve the link, and strip away the drive and path location
of the add-in. It only leaves those if it can't resolve the links.*
>
>
> Thanks,
>
> Avraham Melamed
>
have to be lived with when sharing workbooks, since not everyone will have
the add-in the same location. For me, the smfFixLinks macro has only failed
when the worksheets are protected. But once I turn off the protection,
everything converts fine. Protection may be the reason the "Find and
Replace all" fail as well? I have the smfFixLinks macro saved to a button
on my toolbar, so it's very easy for me to run when I open a workbook that
has the unresolved links.
I know some people in the past have had issues with the add-in being
installed on a network drive. EXCEL seems to handle its usage differently.
But that may vary by which version of EXCEL and/or operating system you're
using.
One other useful macro is smfForceRecalculation. That's because the add-in
saves a copy of a web page before extracting data from the web page. So
just doing a normal recalculation just does and extract from the same saved
copy of the web page. But smfForceRecalculation purges the saved copy of a
web page and gets a fresh copy from the Internet. For some extractions,
where data may be changing constantly during the day, that can be an
essential macro to have on a button on your tool bar.
Also see comments below...
On Thu, Jul 16, 2015 at 1:57 PM, beymelamed@... wrote:
>
> Andy,
>
> I just started using your Excel plugin and am having a hard time.
>
> I followed the instructions and created a file then installed the plugin
> and saw the version number appear.
>
> I also downloaded various samples but am getting #NAME? everywhere.
>
> Looking at the cells with #NAME error, I realize that the reference to the
> .xla is wrong.
>
> Using find and replace does not help either (also does not help when I
> remove the plugin, replace then add the plugin again.)
>
>
>
> For example:
>
> My install folder is:
>
> L:\SMF\ (this is where the .xla file is.)
>
> I downloaded some sample files to L:\SMF\Docs
>
>
>
> I open file
> L:\SMF\Docs\RCHGetTableCell-Template-MSN-Financial-Statements.xls.
>
> In (for example) cell C8 I see:
>
> ='L:\*SMF Add-in*\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker,
> 1,"Period Length")
>
> The cell itself displays #NAME?
>
> I expected it to be:
>
> ='L:\*SMF*\RCH_Stock_Market_Functions.xla'!RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker,
> 1,"Period Length")
>
There should be NOTHING in front of the RCHGetTableCell() function. The
reason it has "SMF Add-in" as the top path is because that's where the
add-in was located when I saved the file. If it had your path, EXCEL would
have stripped off the entire link, because it was able to resolve it.
The L: drive identifier is part of the network drive issue I mentioned.
EXCEL/MicroSoft must handle the drive location differently when it opens
the file. Again, maybe it varies depending on version of EXCEL and O/S.
In any case, after making sure the worksheet isn't protected, doing and
"Find and Replace All" of:
'L:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
...and changing it to be nothing, should have fixed everything. That's all
the smfFixLinks macro does, although it uses a wildcard specification that
doesn't care about drive or path:
'*\RCH_Stock_Market_Functions.xla'!
>
>
> I tried:
>
> 1. 1. Find / Replace (this results in disaster replacement that I
> am not sure how it comes about and of course does not work.)
>
This doesn't make sense to me? Here, it won't even do the replacement if
the formula would be invalid after the replacement is performed.
> 2. 2. Created a folder l:\SMF Add-in and placed the .xla there – No
> improvement
>
Yes. That shouldn't have resolved anything, since the file being opened had
been saved with the add-in on the C: drive.
> 3. 3. Restarted the machine (Windows 7 pro 64 bits, Office 2007)
> and tried (manual) replacements in cells – now I got 'Error' displayed.
>
It may be working perfectly then. As of their website update last year, MSN
is obsolete as a data source. They removed 95% of their data items late
last year.
In any case, for 99.9% of SMF issues, it should be sufficient to simply
exit and restart EXCEL. That's because the add-in is only loaded once -- at
EXCEL startup. And a restart of EXCEL resets all of the VBA environment.
Hmmm. Looks like MSN has added financial statements back out there. I may
update those templates.
Just, as an example, to get the first column of total revenue from the
annal income statement:
=smfConvertData(smfGetTagContent("
http://www.msn.com/en-us/money/stockdetails/financials/income_statement/Annual/fi-MMM","p",1,">Total
Revenue"))
...or, if you're using the beta version of the add-in from the "Works in
Progress" folder, you could drop the smfConvertData() function and use the
pConv parameter of the smfGetTagContent() function:
=smfGetTagContent("
http://www.msn.com/en-us/money/stockdetails/financials/income_statement/Annual/fi-MMM","
Revenue"
> This is happening in most files.
>
> There was only one file where the find / replace took hold and resolved
> the issue (Yahoo-History-
>
> Sometimes, in some files the #NAME! Gets replaced by #VALUE!
>
Without specifics, hard to say why. If the SMF function gets an "Error"
value returned, which is then used in a calculation, that would end up as a
#VALUE! displayed error.
> Having created a folder C:\SMF on another machine and tried some of the
> above, resolved the issue in some, but not all, spreadsheets.
>
I really want to use your great project,
>
Sounds like you're just having trouble using saved workbooks, which is the
resolved links issue from EXCEL.
If you create your own formulas in a new workbook, are you having issues?
*Once you've fixed the unresolved links and save the workbook, you
shouldn'
the location of the add-in). That's because when YOU save the file, it
saves a hard-coded link to the current location of the add-in on your
system. So when you open that workbook, it will find the add-in in that
location and resolve the link, and strip away the drive and path location
of the add-in. It only leaves those if it can't resolve the links.*
>
>
> Thanks,
>
> Avraham Melamed
>
Thu Jul 16, 2015 10:29 pm (PDT) . Posted by:
zbgb952
Using the Excel formula: RCHGetElementNumber($D14,848), I get an error message for several stocks tickers including O. I just checked the Zacks website and they have a value for it.
and they have a value for it. It is now 10:27 PM on 7/16/15. Also, there are other tickers with no Zacks number as well using this formula, those most of the ticker in my spreadsheet do show a Zacks number.
What causes this?
Thanks.
and they have a value for it. It is now 10:27 PM on 7/16/15. Also, there are other tickers with no Zacks number as well using this formula, those most of the ticker in my spreadsheet do show a Zacks number.
What causes this?
Thanks.
Thu Jul 16, 2015 11:57 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
This formula worked fine for me:
=RCHGetElementNumber("O",848)
You may have had a temporary issue with Zacks? Maybe they were updating
things?
You should make sure your Internet Option are set to always get a fresh web
page. And run the smfForceRecalculation macro to force the add-in to get a
fresh copy of the web page.
On Thu, Jul 16, 2015 at 10:29 PM, mb0329@msn.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Using the Excel formula: RCHGetElementNumber($D14,848), I get an error
> message for several stocks tickers including O. I just checked the Zacks
> website and they have a value for it.
>
> and they have a value for it. It is now 10:27 PM on 7/16/15. Also, there
> are other tickers with no Zacks number as well using this formula, those
> most of the ticker in my spreadsheet do show a Zacks number.
>
> What causes this?
>
=RCHGetElementNumber("O",848)
You may have had a temporary issue with Zacks? Maybe they were updating
things?
You should make sure your Internet Option are set to always get a fresh web
page. And run the smfForceRecalculation macro to force the add-in to get a
fresh copy of the web page.
On Thu, Jul 16, 2015 at 10:29 PM, mb0329@msn.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Using the Excel formula: RCHGetElementNumber
> message for several stocks tickers including O. I just checked the Zacks
> website and they have a value for it.
>
> and they have a value for it. It is now 10:27 PM on 7/16/15. Also, there
> are other tickers with no Zacks number as well using this formula, those
> most of the ticker in my spreadsheet do show a Zacks number.
>
> What causes this?
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar