Excel Array Formula Series #14.2: Beginning Stock Analysis

When I add a new series to an Excel stock chart, the added series has stretched some of the high-low lines of the stock H-L-C bars. (they are stretched so as to meet the value of the new series)

Bob Peltier describes on a webpage how to deal with this for some circumstances. However, the example he shows to create a chart with H -L-C bars with an added series is not detailed well on this webpage so I don't understand what he did to get the H-L-C chart with the Index that he shows. It is not clear to me how to fix the problem. The webpage is:

http://peltiertech.com/Excel/Charts/StockChartPlus.html

Maybe I missed something.

Can anyone in this forum offer a suggestion?

Thanks,

Art

Hello,

This post is not about syntax or code. It's more about how to address a problem. I have several workbooks. Since I work for an equity trading firm, all my workbooks have a list of stocks. They follow two forms. I'll post the forms in a following post to make it easy to read. There were my first Excel Workbooks; so I'm wondering if there is a more effecient way to solve the problem.

Basically at the end of the day I need a list of stocks. I usually either use the autofilter (form A) or use a formula in another sheet to display the value of column A if flags are set or empty if not. Then I use an autofilter on that to print out.

Any ideas?

Ok, I have downloaded 15 stocks' history and I need to find the portfolio returns (both equally weighted and price weighted), and run regression on them against the S&P 500 returns to find Beta (for both price weighted & equally weighted). I have all the data I need (Closing Stock Price, and % change) for the stocks and the S&P 500. What I don't know how to do is find the returns. Also I know where to go to run the regression model but I'm not sure what to plug in for X and Y. What it comes down to is, I have to run regression with the equally weighted returns against the S&P 500 returns to find Beta 1, and then run regression with the price weighted returns against the S&P 500 returns to find Beta 2. Anybody know how?

Hello;

1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43

Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...

2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x

Your help would be greatly appreciated.

Regards.

How to create a number series within an array formula, I mean, the series starts with 1 and will finish with 8 for example;
{1;2;3;4;5;6;7;8} - It will be the result within my array formula.
In Aij matrix means i=1 to 8 {i+1;i+1;i+1;i+1;i+1;i+1;i+1;i+1}.
I need to retrieve it within an array formula.

How can I do that just using an array formula.



Luthius

I have several sequences of data and I'd like to do statistical analysis of trends within these sequences.

For simplicity's sake I'll illustrate this using letters:


Sequence 1: A B L A G F C
Sequence 2: X B D K W L H K
Sequence 3: A B L H K W E N
Sequence 4: X B L A G C H K
Sequence 5: J L H B K W E N
etc...

The actual patterns are non-random and have several very clear patterns and tendencies.

I'd like to do some number crunching to determine things like:

1.) What are the most common sequences (pairs, trios), etc. of letters? (for example, in the examples above, "LAG" occurs in both sequence 1 and 4.
2.) What are the most common opening patterns (as in, the first 2-4 letters) (for example, Sequence 1 and 3 both begin with ABL, sequence 2 & 4 both begin with XB.
3.) What are the most common closing patterns (last 3-4 letters) (for example, WEN is the most common ending, HK is the second most common)
4.) When the letter L occurs, what are the most common letters preceding / succeeding it? (e.g., L is usually followed by H, and sometimes by A. W is always preceded by K)
5.) When the letters D and F occur in succession, what are the most common letters preceding / succeeding this pair?
6.) In all the sequences, what are the most/least common letters (in order)?
etc.

...And many other similar questions...

The ultimate goal: given a new sequence with blanks in it, I would like to make intelligent guess as to the most likely letters to fill those blanks.

Thanks

I have a list like this:

Sheet1

  A B C 1 Alfa Status D 75 2 Bravo Status A   3 Charlie Status B 483 4 Delta Status B 78 5 Echo Status C   6 Foxtrot Status A   7 Golf Status B   8 Hotel Status B 12 9 India Status A   10 Juliett Status A 405 11 Kilo Status C   12 Lima Status B   13 Mike Status A   14 November Status B 27 15 Oscar Status B 13 16 Papa Status B   17 Quebec Status C 140 18 Romeo Status B 426 19 Sierra Status C 484 20 Tango Status D 197

Excel tables to the web >> Excel Jeanie HTML 4

I want to single out all Status B values, and put them in a new table in descending order.

In this case, it would look like this:

Sheet1

  A B C 10 Charlie Status B 483 11 Romeo Status B 426 12 Delta Status B 78 13 November Status B 27 14 Oscar Status B 13 15 Hotel Status B 12

Excel tables to the web >> Excel Jeanie HTML 4

So I need to ignore blank values, determine which row has the highest quantity, and then return the value in column A and column C.

I know I can use array formulas to a certain extent, but I'm stumped as to how to store the row of the highest values in the array while ignoring blanks. Any ideas?

Can anyone explain to me how to add an additional line to my stock chart on sheet 1. The data for the additional series is in IA11:IT11 on the Prices for Charts tab.

I want to add an index to a stock chart, but am having trouble doing in Excel 2007. This website shows how to do it using a XY scatter chart type for earlier Excel versions: http://peltiertech.com/Excel/Charts/StockChartPlus.html. After hours of trying to figure out what the equivalent 2007 keystrokes are, I've concluded Excel has made this impossible for its new version. Am I wrong?

Hi,

For my job I was asked to create a chart showing a loss tolerance range, expressed in basis points, with next year's expected loss rate plotted as a point within the range. Until today I was able to do this using the stock chart (high-low-close), with high and low representing either ends of the loss tolerance range, and "close" representing next year's planned losses.

Today I was asked to add the last two years' actual loss rates to the range as well, and I can't for the life of me figure out how to do this without manually dropping an auto shape on the range to approximate where it would be.

Is it possible to accomplish what I'm trying to do?

I quickly drew up what the final product should look like in Paint, which you can find attached.

Any help with this would be hugely appreciated! Thanks in advance!