Excel help

Cheezdoodles

+ 1
Veteran
I got price data time series for stocks in excel.

Problem is that the data is downloaded from datastream, and this results in a desimal error (or rather, it forgets to put desimals into the number, the end result being that that instead of having a stock price of 12,12345 it shows as 12112345, meaning that the place where the , (or . depending on region) is supposed to be, is not there).

Is there an easy way to make excel put a , behind the first 2 numbers in each time series?

(cannot do it by hand, as its 2600+ observations for 20 different stocks)
 
Hum, I don't have Excel installed at home, so can't test it, but you should be able to open the properties for the cell and change how numbers are displayed at. You should be able to pretty easily adjust the commas and how many of them should be displayed...
 
Fill 2600 cells (in a column or row just like the original data) with the factor you want to multiply by, e.g. 0.000001. Then highlight your errant data, copy to clipboard, highlight first of the multiplication-factor cells, paste-special and specify the "multiply" option.

Brute force for the win :p

Jawed
 
I thought of this, but the problem is that the amount of numbers in each entry varies, so i cannot multiply by a constant factor to solve the problem.

What would solve the problem, is if i could instruct excel to somehow put a , after the 2 first numbers in each entry. Is something like this doable?
 
Last edited by a moderator:
Some VBA:

Code:
Function FixDecimal(Bad As Long) As Double
 
BadString = CStr(Bad)
FixDecimal = CDbl(Left(BadString, 2) & "." & Mid(BadString, 3))
 
End Function

And call it in a set of 2600 cells, e.g. =FixDecimal(A1) in cell B1, etc.

I'm reasonably sure the code must use "." and not your Windows-localised "," so don't edit that (unless I'm wrong, of course!).

To create the function, hit Alt-F11 and in the editor choose Module from the Insert menu. Then paste that code there.

Jawed
 
Dang Jawed that doesn't seem that hard.

I have tons and tons of data that I might need to mess with in excel. I will have to look into using some VBA functions to deal with it. Previously I put stuff into matlab instead, but that has its own problems with large datasets as well.
 
:LOL: my circuitous solution has at least inspired someone to program within Excel :LOL:
Bastard! :devilish: Although, having had to do quite a bit of SAS lately, I almost long for VBA. That package is powerful, but man is it convoluted...
 
So I don't know if you guys could help me out on my problem, at least the proper words to search for in google.

Should I be looking for visual basic tutorials or excel macro tutorials?


My problem is actually fairly simple from a coding perspective.

What I have is a bunch of data from which I need to pull certain values. The location of the values is related to the step and test cycle number.

What I need is a function that searches through the step number column and when it changes from say step 12 --> a higher number it goes over two columns and copies a value. It will have to do this repeatedly in the same column so it is not a one time thing. I have found plenty of examples in searching for a specific value, but not for comparative values. Is there a better search term I should use in google?
 
What I want is to find when the value in a specific column turns from 12--> a number >12

Usually it goes step 12-->13, but sometimes to 14

So the code in generic language is

Code:
for x=0:max row
if Cell(x,column)==12 and Cell(x+1,column)>12
 
set activecell(x,column)
ActiveCell.Offset(2, 1).Select
copy that cell then write it to a new worksheet in a specific row column position

end if
end

Obviously I could probably just set the active cell to the one I want based on the offset already.

It is more a question of learning the basic structure of VBA in comparison to other languages I am usd to I guess.

That site though looks good it has some nice intro material to use. I just need the simple stuff like to ensure I am selecting the right cells and so forth.
 
So the concepts you should investigate are:
  • for each - allows you to iterate through the cells in a range
  • activesheet - worksheet where the function was called from
  • worksheets - access worksheets in the current workbook, e.g. by name (methods include adding a new worksheet)
  • with - useful for de-cluttering long-winded object references
That list goes beyond what you need for this function but it'll get you right to the heart of using VBA in Excel.

Jawed
 
Don't want to discourage you from programming at all of course, but I think that the highlighting can fairly easily be done with the conditional formatting option.
 
What I want is to find when the value in a specific column turns from 12--> a number >12

Usually it goes step 12-->13, but sometimes to 14

.
I haven't really been following the thread, but could you do something like have a column of the differences which are made into a step function, ie. if(XA-1 != XA, 1, 0), and then use SUM(start_of_column, CurrentPos) to create a column of index values?
 
I actually have been having pretty good success with it. And it is a bit more complicated than I let on, but what I said was the basics as I needed them. I haven't really coded in VB, VBA, or even VC and the syntax is a little different than borland c++, or matlab which is what I am used to.

So far I have the code doing almost everything I need. It is missing one things.

I need to make it a portable macro. I made sub routines that tested everything so I could figure out how to create a worksheet, read a specific value, write a specific value etc.. then I combined all of them into a final subroutine. I think I need to make it into a function and save it as XLA and delete the worksheet or something along those lines. I will have to make a number of other programs as well since there are four unique test procedures. Or I could have a message box that prompts the user to select what type of test it was and select the correct method to decode it.
 
Last edited by a moderator:
Back
Top