EXCEL - issues-tips-links

[ayla]

Thought I would start a thread that would discuss Excel which is our main tool that we use to analyze the market indicators (which are also tools).

I haven't been charting for very long. It does take a bit of time but there is so much info on the web that can help, wanted to tell others that if they have the time and if you need a good place to start at the VERY VERY beginning for charting, I think there is a good explanation here (and probably other places also) at:

http://atom.physics.calpoly.edu/Excel/excel.html

Lots more I could say and ask about Excel. Will save for other posts.

 

[fabijo]

I just learned an easy and useful way to overcome the way copying and pasting formulas in Excel can sometimes work against you. Let's say you are working with data in column A. In columns B through M, you want to put different formulas in that work with data in column A. You want all the formulas to be similar, but only one variable is different. A perfect example is moving averages. Maybe you want each column to be a different moving average number.

If you just copy formula from B and paste it into C through M, all references to column A will switch to B, C, ... , L. What you could do is use absolute references. If cell B5 referred to cell A5, and you copied it into C5, without absolute references, C5 will instead say B5, where you wanted A5. To fix that, just use $A5 within B5. Copying and pasting that will make column A the absolute reference, while the row 5 is relative. To make the cell itself absolute, just use $A$5. This might be useful if you want the cell to hold a reference, like the number of days in a moving average.

If what I just said makes no sense, Excel Help might be more useful. Just search for "absolute reference." :blink:

 

[ChemEng]

Absolute references help you fight the problem you are working instead of fighting Excel to get it in a worksheet. Although, I can never remember which absolute reference I need! Is it $A1 or A$1? (Its even worse when I need to use both in the same equation.) I always have to play with it to see which one moves like I want.

 

[Sunnyskies]

Timely subject. I have a spreadsheet which has the daily share prices for each fund. In separate sheets, I created a graph which plots the daily prices (separate sheet/graph for each fund). Each day I update the daily share prices (I insert the new data on line 2 which forces all the other data down a line). When the other data is forced down a line, the source data references for the graphs change also. I have to right click in each graph and update the "source data" so that it will include the new data (line 2 on my original spreadsheet). I have tried to remove the absolute references in the source data box, but the absolutes return everytime.

I suppose I could record a macro to automate the process, but sometimes those things mess me up. Any ideas to make the graphs automatically update when I insert new data?

Also, I change the color of the data point to reflect it I bought (green) or sold (red). Is there a way to indicate this on the data spreadsheet and have the graph plot that color automatically. What I found is that if I cause the graph to replot itself, then I lose the colors that I had put in. Hope this makes sense.

 

[ChemEng]

Heres a tutorial about how to put arrows on specific points. You may be able to modify it to what you are trying to do.
http://peltiertech.com/Excel/Charts/PointAtPoints.html

When I am inputting price data into excel I always use 2 spreadsheets. The first spreadsheet just holds the price data. Insert a row and then paste it in. The second spreadsheet holds all the analytical stuff. I copy the data from the first spreadsheet and then paste it over the data thats already there. This approach will preserve any data source references you are trying to maintain while updating the prices.

 

[jayhawker]

Timely subject. I have a spreadsheet which has the daily share prices for each fund. In separate sheets, I created a graph which plots the daily prices (separate sheet/graph for each fund). Each day I update the daily share prices (I insert the new data on line 2 which forces all the other data down a line). When the other data is forced down a line, the source data references for the graphs change also. I have to right click in each graph and update the "source data" so that it will include the new data (line 2 on my original spreadsheet). I have tried to remove the absolute references in the source data box, but the absolutes return everytime.

I suppose I could record a macro to automate the process, but sometimes those things mess me up. Any ideas to make the graphs automatically update when I insert new data?

Also, I change the color of the data point to reflect it I bought (green) or sold (red). Is there a way to indicate this on the data spreadsheet and have the graph plot that color automatically. What I found is that if I cause the graph to replot itself, then I lose the colors that I had put in. Hope this makes sense.

Try reversing the order of the data. Instead of inserting the latest share prices in row 2, make row 2 the oldest data and go down. New data would be entered on the next available row. Change your graph's source data to refer to rows from 2 to past the next available row. Each time you add new share data in the next available row the graph will automatically reflect the new data and shouldn't change any previous formatting.

Ed

 

[Scout333]

Sunny, you might try using a Paste Special. Highlight cells you want to use i.e. share prices, Select Copy, Paste Special, Go to source for graphs, and Select Paste Link. on mine its in the bottom left corner of screen. If this doesn't work try Excel Help and type in Paste Special. Good luck!

 

[Sunnyskies]

Thanks for the tips. I use Paste Special frequently, but hadn't tried it for in the source box. I'll take a look.

Ed -- I've thought about putting the new data at the bottom (now don't laugh) -- but wouldn't the graphs plot it backwards? In otherwords, January's prices would be on the right edge of the graph rather than the left? I could probably get use to it I guess.

 

[jayhawker]

Thanks for the tips. I use Paste Special frequently, but hadn't tried it for in the source box. I'll take a look.

Ed -- I've thought about putting the new data at the bottom (now don't laugh) -- but wouldn't the graphs plot it backwards? In otherwords, January's prices would be on the right edge of the graph rather than the left? I could probably get use to it I guess.

LOL! (Sorry!)

No, it shouldn't plot it backwards. I'm surprised it's not backwards now! You can choose to plot backwards - and that may the current status. It's a format chart option.