Rabu, 30 April 2014

[smf_addin] Digest Number 3054

15 Messages

Digest #3054
1a
Re: historical stock prices by "Randy Harmelink" rharmelink
2b
2e
2f
advfn function computer freezes by "Nikola Ganev" ganevniko
2g
Re: advfn function computer freezes by "Randy Harmelink" rharmelink
2h
Re: advfn function computer freezes by "Nikola Ganev" ganevniko
2i
Re: advfn function computer freezes by "Randy Harmelink" rharmelink
3a
Excel 2010 stopped after protected view by "Shri K" truebangalorean
3b
Re: Excel 2010 stopped after protected view by "Randy Harmelink" rharmelink
4
chartapi by dudescholar

Messages

Wed Apr 30, 2014 7:58 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

If you use any date field parameters, you need to use all six date field
parameters.

On Tue, Apr 29, 2014 at 10:01 PM, <lawrence.leesh@gmail.com> wrote:

>
> I used this formula
> =RCHGetYahooHistory("4677.KL",2009,1,1,2014)
> and it says "Something wrong with dates..."
>
> Did I make a mistake somewhere?
>
>

Wed Apr 30, 2014 8:33 am (PDT) . Posted by:

ssztaba

Dear Randy,

I'm used to using the GetYahooQuotes function as an array, for example {=RCHGetYahooQuotes(B7:B206,C6:D6,,NOW())} -- where the B Column has stock symbols and C6 has n, and D6 has l1. This very quickly gives me the Company name and last price of all the symbols.

I've tried to use this same approach to get Dividend and Earnings data, but it's not working for me:
{=RCHGetElementNumber(B7:B206,E6:I6,,NOW())}
Again the B Column has the stock symbols, and E6 through I6 has 992 991 4924 4925 1285 (looking for Ex Div date; Div Pay Date; Div $; Div Yield; and Earnings Date)

Am I doing something incorrect in this approach ?? Entering the formulas directly into each Cell -- e.g. RCHGetElementNumber($B7,E$6) -- does work, but is very, very slow, and so I was hoping to use the Array.

Any guidance ??

Thanks

Stan Sztaba



Wed Apr 30, 2014 9:08 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The reason RCHGetYahooQuotes() is faster when array-entered is because it
passes parameters to Yahoo that allows Yahoo to create a file with all of
the data on it. All in one Internet access.

There isn't an equivalent Internet process for RCHGetElementNumber() data
retrievals. The reason RCHGetElementNumber() is slow is BECAUSE it has to
access the Internet for each new web page from which data is being
retrieved. Allowing the function to be array-entered wouldn't change that.

In general, I've only used array-entered functions where it allows the
advantage. Otherwise, in general, array-entered functions are a pain,
because you can't do things like sort, insert columns or rows, etc.

Having said that, you could look at using the smfUpdateDownloadTable macro.
It loads values into the workbook instead of using recalculating formulas.
Then, it only updates things when you run the macro. For things that change
quarterly, this can save a lot of time by avoiding refreshing them all from
the Internet every time you open the workbook.

See the "Tips and FAQs" area for more info on this macro. I usually just
start from the sample template:

https://groups.yahoo.com/neo/groups/smf_addin/files/Templates%20and%20Examples/smfUpdateDownloadTable/

On Wed, Apr 30, 2014 at 8:33 AM, <ssjurik@optonline.net> wrote:

>
> I'm used to using the GetYahooQuotes function as an array, for example
> {=RCHGetYahooQuotes(B7:B206,C6:D6,,NOW())} -- where the B Column has stock
> symbols and C6 has n, and D6 has l1. This very quickly gives me the
> Company name and last price of all the symbols.
>
> I've tried to use this same approach to get Dividend and Earnings data,
> but it's not working for me:
> {=RCHGetElementNumber(B7:B206,E6:I6,,NOW())}
> Again the B Column has the stock symbols, and E6 through I6 has 992 991
> 4924 4925 1285 (looking for Ex Div date; Div Pay Date; Div $; Div
> Yield; and Earnings Date)
>
> Am I doing something incorrect in this approach ?? Entering the
> formulas directly into each Cell -- e.g. RCHGetElementNumber($B7,E$6) --
> does work, but is very, very slow, and so I was hoping to use the Array.
>
> Any guidance ??
>

Wed Apr 30, 2014 9:55 am (PDT) . Posted by:

ssztaba

Randy,

The Sample Template is wonderful -- thank you.

Using the Macro is also great since I don't need to update Earnings and Dividend data every time the workbook is opened.

I don't see the Macro code, so a couple of questions on restrictions:

-- should symbols always be in Column B ?? and
-- should Element #s always be in Row 2 ??

-- any restrictions on having blank cells ?? ( e.g. if I wanted to divide the Yearly $ amount of the Dividend (element 4924) by 4 to get the quarterly Div amount, can I have this formula cell in between two elements (4924 and 1285 for example), or will that mess up results ??

Thanks as always

Stan Sztaba

Wed Apr 30, 2014 10:17 am (PDT) . Posted by:

ssztaba

Randy,

I found the "Tips and FAQs" instructions which answers my questions, so there's no need for you to reply.

Thanks again

Stan Sztaba

Wed Apr 30, 2014 10:19 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Wed, Apr 30, 2014 at 9:55 AM, <ssjurik@optonline.net> wrote:

>
> The Sample Template is wonderful -- thank you.
>
> Using the Macro is also great since I don't need to update Earnings and
> Dividend data every time the workbook is opened.
>

That's the main reason I use it. It's nice to be able to open the workbook
and see the previous values there, without having to have them all
recalculate.

And if I do want them to recalculate, I just need to run the macro (I have
it as a button in my Quick Access Toolbar, right next to the button for
smfForceRecalculation).

> I don't see the Macro code, so a couple of questions on restrictions:
>
> -- should symbols always be in Column B ?? and
> -- should Element #s always be in Row 2 ??
>

It's all keyed on the cell that is named "Ticker". It continues down the
column just underneath that until it finds a blank cell in a row of that
column. And it continues across the row, just above it (so you can enter
your own headings), until it finds a blanks cell in column of that row.

So if you want a partial update, you can just insert a row below those you
want updated. For example, I may have a sort field I use to indicate when
some things need updating, sort by that field, insert my blank row, then
run the macro. Then delete the blank row. Whatever I wanted updated gets
updated.

One user did create a process so that you could highlight a row you wanted
to update. I've always meant to go back and implement that.

-- any restrictions on having blank cells ?? ( e.g. if I wanted to
divide the Yearly $ amount of the Dividend (element 4924) by 4 to get the
quarterly Div amount, can I have this formula cell in between two elements
(4924 and 1285 for example), or will that mess up results ??

Since the blank cell indicates where the macro is to stop, that would be a
problem. However, if you put an "X" in the column instead of an element
number, it would bypass updating anything in that column, so you could use
your own formula in the column of that table.

Note the sample did have some pseudo-formulas instead of element numbers.
You can even get more than that. Let's suppose you have the annual dividend
amount in a column. The pseudo-formula for the next column, to compute the
quarterly amount, could be:

~~~1~~~/4

The "~~~1~~~" says to take the value that is one column to the left of the
current column. Then you don't have to worry about copying the formula down
if you added more tickers.

In one of my worksheets, I have a pseudo-formula that looks like this, to
compute the extrinsic value on an ITM call:

="IF(ISNUMBER(~~~"&COLUMNS(K:N)-1&"~~~),MAX(0,~~~"&COLUMNS(L:N)-1&"~~~+~~~"&COLUMNS(K:N)-1&"~~~-~~~"&COLUMNS($G:N)-1&"~~~),0)"

...which calculates out to be:

IF(ISNUMBER(~~~3~~~),MAX(0,~~~2~~~+~~~3~~~-~~~7~~~),0)

I use the COLUMNS() function in the first instance because it means I don't
have to update my column references in the formula if I insert a column.
You can also use named ranges in your pseudo formulas. For example:

ISNA(MATCH("~~~1~~~",rASIN,0))*(LEN("~~~1~~~")>0)

Wed Apr 30, 2014 11:59 am (PDT) . Posted by:

"Nikola Ganev" ganevniko

Hi,
 
I am running a macro that inputs a ticker copies the data which is a couple of item lines from the advfn function to another column and moves to the next ticker. I would like to do it for many companies but when I ask the macro to process a lot of tickers it usually freezes after 3 or 4 companies.
Do you have any idea how to fix it?
 
Thank You for your help

Wed Apr 30, 2014 12:14 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

With that limited description, not a clue.

I've had situations where EXCEL appears to freeze up, but it's just taking
a while to run. In those situations, I like to add notices to the status
bar to show me the progress of the macro.

I had one process the other day that ran for nearly three hours (retrieving
search results from Amazon). It was placing values into the workbook after
each iteration. I changed it to load things into an array and then output
the array into the workbook at the end. Now it runs just over a minute.

On Wed, Apr 30, 2014 at 11:56 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:

>
> I am running a macro that inputs a ticker copies the data which is a
> couple of item lines from the advfn function to another column and moves to
> the next ticker. I would like to do it for many companies but when I ask
> the macro to process a lot of tickers it usually freezes after 3 or 4
> companies.
> Do you have any idea how to fix it?
>
>

Wed Apr 30, 2014 4:40 pm (PDT) . Posted by:

"Nikola Ganev" ganevniko

An array would be a very good solution I guess. Can I create arrays with advfn function? I would like to have one (or more) tickers for a selected quarter and couple of item lines ( lets say revenue, ebit, ebitda, lt debt, dividends and shares outstanding)
 
Thank you

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, April 30, 2014 3:14 PM
Subject: Re: [smf_addin] advfn function computer freezes

 
With that limited description, not a clue.

I've had situations where EXCEL appears to freeze up, but it's just taking a while to run. In those situations, I like to add notices to the status bar to show me the progress of the macro.

I had one process the other day that ran for nearly three hours (retrieving search results from Amazon). It was placing values into the workbook after each iteration. I changed it to load things into an array and then output the array into the workbook at the end. Now it runs just over a minute.

On Wed, Apr 30, 2014 at 11:56 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:

 
>
>I am running a macro that inputs a ticker copies the data which is a couple of item lines from the advfn function to another column and moves to the next ticker. I would like to do it for many companies but when I ask the macro to process a lot of tickers it usually freezes after 3 or 4 companies.
>Do you have any idea how to fix it?

>

Wed Apr 30, 2014 5:13 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

For my purposes, the array in the macro was to prevent interacting with the
worksheet continuously. I'm not talking about array-entering a formula.

Some advice I gave someone else today -- you could look at using the
smfUpdateDownloadTable macro. It loads values into the workbook instead of
using recalculating formulas. Then, it only updates things when you run the
macro. For things that change quarterly, this can save a lot of time by
avoiding refreshing them all from the Internet every time you open the
workbook.

See the "Tips and FAQs" area for more info on this macro. I usually just
start from the sample template:

https://groups.yahoo.com/neo/groups/smf_addin/files/Templates%20and%20Examples/smfUpdateDownloadTable/

On Wed, Apr 30, 2014 at 4:37 PM, Nikola Ganev <ganevniko@yahoo.com> wrote:

>
> An array would be a very good solution I guess. Can I create arrays with
> advfn function? I would like to have one (or more) tickers for a selected
> quarter and couple of item lines ( lets say revenue, ebit, ebitda, lt debt,
> dividends and shares outstanding)
>
>

Wed Apr 30, 2014 2:42 pm (PDT) . Posted by:

"Shri K" truebangalorean

Randy, Gurus,

I have been using SMF_Addin for quite sometime and in particular RCHgetYahooHistory(). While the excel 2010 file was open, I downloaded a file from St.Louis FRED site and the excel went into Protected mode and doesn't download data via RCHGetYahooHistory anymore.

I went into excel trust center and played around with disabling protected view, but do not seem to get RCHGetYahooHistory to work.. the cells are blank .. restarted windows as well ..

Any clues? Other RCH functions seem to work..

Thank you,

Shri

Wed Apr 30, 2014 3:05 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Unfortunately, I don't have any experience with EXCEL 2010 and protected
view.

One thing different about RCHGetYahooHistory() is that it is opening a
downloaded CSV file, instead of grabbing data from a web page. If that is
the problem, RCHGetYahooQuotes() should also be a problem.

Hopefully, that will give you a clue as to the problem?

On Wed, Apr 30, 2014 at 2:42 PM, Shri K <truebangalorean@yahoo.com> wrote:

>
> I have been using SMF_Addin for quite sometime and in particular
> RCHgetYahooHistory(). While the excel 2010 file was open, I downloaded a
> file from St.Louis FRED site and the excel went into Protected mode and
> doesn't download data via RCHGetYahooHistory anymore.
>
> I went into excel trust center and played around with disabling protected
> view, but do not seem to get RCHGetYahooHistory to work.. the cells are
> blank .. restarted windows as well ..
>
> Any clues? Other RCH functions seem to work..
>
>

Wed Apr 30, 2014 4:15 pm (PDT) . Posted by:

mjlwis

Shiri,

I came hear because of similar problems that started this afternoon. I use Excel 2007. I tried to update my files multiple times including rebooting my system. They still failed. After reading your message, I decided to try my two spreadsheets again, before adding my voice to your message. To my pleasant surprise it worked. Tried the second file and it too worked. I am thinking Yahoo may have had a problem or was making a change.

Hopefully, your files will work too.

Mike

Wed Apr 30, 2014 5:38 pm (PDT) . Posted by:

dudescholar

Did a search of the group for references to chartapi and didn't get any hits. I've been using this lately because it's easier than dealing with eSignal to extract intraday data and the limitation of only providing 5 minute data is good enough for what I'm doing. There's no description anywhere of how it works, but I've been downloading 20 days of 5 minute data (20d) or 2 years of of daily data (2y).



I was interested in calculating the ATR of a pair spread like "1.4 * CCL - RCL" using intraday data. All the charting software only looks at the opening and closing of a pair for figuring out the high or low of the day on these kinds of spreads so ATR calculation on daily data of pair spreads is always low. 5 minute data results is very close to 1 minute data results for a 14 day ATR calc.



= smfGetCSVFile("http://chartapi.finance.yahoo.com/instrument/1.0/SPY/chartdata;type=quote;range=20d/csv", pDim1:=kRows, pDim2:=kCols)


Steve




READ MORE....