On Sat, 22 Mar 2008 16:36:16 -0400, "Jeff" wrote:

Ron Rosenfeld wrote:
On Sat, 22 Mar 2008 14:38:38 -0400, "Jeff" wrote:

Hi

I am doing my taxes and need to find a way to convert my broker's
1099B (stocks transactions during the year) from a text pdf form or
Word document into an excel format. Is there a way to do this?

I can receive my 1099B data from my broker as a pdf or as a printed
statement. But I need it in the form of a Excel spreadsheet with
multiple columns. As a small example (the list is much longer which
is why I am asking), I put a representative sample copied and pasted
from the pdf into Word the same data copied and pasted from the pdf
to an Excel document. They are at:
http://www.orthohelp.com/1099b.doc
http://www.orthohelp.com/1099b.xls

Is there a way to convert this into an excel spreadsheet format with
data columns that I can manipulate?

Thanks.

Jeff


Since you can get the data into the excel worksheet, a macro can
parse it up the way you would like.

Of course, you could use the Data/Text to columns wizard, but you'll
have a fair amount of cleanup to do after since you have an
inconsistent "$", and also no <space between the end of the amount
and the beginning of the company name (and I presume some company
names could start with a digit).

I have assumed that all amounts are reported to two decimal digits,
as was the case on the example you posted.

If your example is not representative, some changes may need to be
made.

In any event, once you copy the data into your worksheet, run the
F1099b Macro and it should parse things out.

To enter the macro, <alt-F11 opens the VBEditor. Ensure your
project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, first SELECT the cells that need to be Parsed.
Then,<alt-F8 opens the macro dialog box. Select F1099b and <RUN.

=====================================
Option Explicit
Sub F1099b()
Dim rg As Range, c As Range
Dim Str As String, I As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = _
"^(\S+)\s(\S+)\s(\S+)\s(\S+)[\s$]+(\S+)[\s$]+([\d,]+\.\d\d)(.*$)"
'could setup rg in a variety of ways
Set rg = Selection
For Each c In rg
Str = c.Value
If re.test(Str) = True Then
Set mc = re.Execute(Str)
For I = 1 To mc(0).submatches.Count
Select Case I
Case 1, 3
c.Offset(0, I).NumberFormat = "@"
Case 2
c.Offset(0, I).NumberFormat = "mm/dd/yyyy"
Case Else
c.Offset(0, I).NumberFormat = "General"
End Select
c.Offset(0, I).Value = mc(0).submatches(I - 1)
Next I
End If
Next c
End Sub
=========================
--ron

Dear Ron

Thank you so much for helping.

I tried it as described on the xls sample file I had posted above, but I
got a Microsoft Visual Basic error:
=========
Run-time error '432':
File name or class name not found during Automation operation
===========
Debug button took me to the following highlighted line (yellow):
Set re = CreateObject("vbscript.regexp")

For the record, I am using Excel 2002 with the latest Office updates.
It is possible I have the macros disabled from some past MS security
measure but am not sure.

Jeff


I don't understand why you got that error. I've not read of that happening
before with createobject. I would have thought that if you had macros
disabled, you would have seen some message cluing you in.

In any event, try this:

1. When in the VB Editor, from the main menu bar select Tools/References and
then select "Microsoft VBScript Regular Expressions 5.5" from the dropdown
list.

2. Replace the code I gave you with the code below:

================================================== =
Option Explicit
Sub F1099b()
'Requires reference to be set to
'Microsoft VBScript Regular Expressions 5.5
Dim rg As Range, c As Range
Dim Str As String, I As Long
Dim re As RegExp, mc As MatchCollection
Set re = New RegExp
re.Global = True
re.Pattern = _
"^(\S+)\s(\S+)\s(\S+)\s(\S+)[\s$]+(\S+)[\s$]+([\d,]+\.\d\d)(.*$)"
'could setup rg in a variety of ways
Set rg = Selection
For Each c In rg
Str = c.Value
If re.test(Str) = True Then
Set mc = re.Execute(Str)
For I = 1 To mc(0).submatches.Count
Select Case I
Case 1, 3
c.Offset(0, I).NumberFormat = "@"
Case 2
c.Offset(0, I).NumberFormat = "mm/dd/yyyy"
Case Else
c.Offset(0, I).NumberFormat = "General"
End Select
c.Offset(0, I).Value = mc(0).submatches(I - 1)
Next I
End If
Next c
End Sub
=================================================
--ron