First some background
So I got bored and decided I wanted to look for sites that graphically tracked the change in a stocks EPS over time. I really want to track atleast 5 years worth of data so I have enough data points. www.earnings.com gives me the necessary data however when i cut and paste the earnings history into excel for some reason the earnings figures are pasted as text or something that doesnt allow me to treat them as I otherwise a number.
For example
A1= $0.27
A2= $0.35
I want to add A1 and A2, however when i do it simply displays it as #VALUE
I have tryed using text to column, I have tryed multiplying the selected cells by 1, and i have even tryed the following macro.
Sub Enter_Values()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub
How do I convert these so i can run operations on the data?! Thanks.
After playing around with it some more it appears that what is causing the problem is a space at the end of the string. For some reason when i use the TRIM function it still does not work correctly. Again any help would greatly be appreciated. I have ran into this problem many times and I absolutely hate having go through my hand and retype it.
create an array that'll take that data and then cycle it through a For loop each time declaring it as Integer or Long since now it seems to be a String type of variable. Not 100% if this'll work but give it a shot
quick google result: http://www.contextures.com/xlDataEntry03.html
I actually found a macro to remove all spaces. It worked.
trade4size, where did u find the macro?
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
Used this code
Sub RemoveAllSpaces() 'David McRitchie 2000-10-24 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _ Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True Selection.SpecialCells(xlConstants).Replace What:=Chr(32), _ Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Pretty helpfull feature, if you need to pull large amounts of data from tables on web sites and when you cut and paste it comes out as HTML and not numbers this will fix the problem. This will save me hours.
Now i just cant figure out how to save the macro for general use. since it will be something i do pretty common.
Sub ConvertToNum() Dim bCell as Range, TheNum as String For Each bCell in Selection TheNum = Replace(Range(bCell).value, " ", "") TheNum = Replace(TheNum,"$", "") Range(bCell).value = cdbl(TheNum) Next bCell End Sub
Assign an easy short-cut key to your macro, makes it alot easier. That should work on every cell in the range you select.
Correction:
Sub ConvertToNum() Dim bCell As Range, TheNum As String For Each bCell In Selection TheNum = Replace(bCell, " ", "") TheNum = Replace(TheNum, "$", "") bCell.Value = CDbl(TheNum) bCell.NumberFormat = "0.00" Next bCell End Sub
also try doing a Web Query from Yahoo finance, it'll save you the cut & paste and can directly import it in a number format into an Excel worksheet.
T4S, you should be either be drinking or moving forum topics instead of doing this :)...btw how is your internship search going? shoot me a PM.
Elan- I cant figure out how to assign a short cut key to the macro. Im still very new to VBA and macros but as i tend to need more features i learn new things.
Stern- I was drinking when i did this.... (excel)models & bottles BABY! Ohhhh yeah.
ALT+T+M+M or Alt+F8 or goto TOOLS in the menu, then MACRO then MACROS
Once the Macro dialog window comes up, select the macro you want to assign a shortcut key to and then click the OPTIONS button or hit ALT+O. This will let you assign a shortcut key to the macro. VERY VERY helpful.
CTRL+ whatever key you assign
Will banking analysts be expected to do this? If so, I am screwed lol.
Get Excel VBA Programming for Dummies.
Its very easy and straightforward. Dont be intimidated cuz it looks like programming, it barely is.
Quis nihil quas perspiciatis cum esse et iste. Ullam dolores maxime inventore nemo nulla rem eos. Facere dolor atque suscipit architecto porro dignissimos placeat. Ut explicabo ex enim dolorem similique. Qui magni et dolores saepe sunt sit laudantium.
Qui possimus iste ratione facilis architecto quod et. Molestias qui cumque et est rem sed. Et quia accusamus voluptatum quia hic.
Autem dolores autem veritatis. Quas illo minus quasi qui.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...