Data Scraping --> Write to Excel Range or CSV Question [SOLVED]

I created a bot that loops through a list of ticker symbols on an excel spreadsheet, types them into Morningstar and grab the quarterly results of the previous year. This appears to work as planned. However the way the data is set up on the website doesn’t match my excel file layout.

For example: My excel sheet looks like
Ticker Name Q1 Q2 Q3 Q4
Ticker 2
Ticker 3

However the set up of the web table cause the input to be placed:

Q1 data
Q2 data
Q3 data
Q 4 data

Therefore the Q1 is next to ticker 1 and Q2 (for ticker 1) is next to ticker 2

Is there anyway to Transpose(such as the paste special type in excel) this data so that it goes across the row instead of down the column?

It’s not pretty, but why not just use a few assign variables to fix it up?

Read range in excel to get format you like and save as OutputTable
For each row in OutputTable
  [get info for ticker and save as InputDataTable]
  Assign row.item("Q1") = InputDataTable.rows(0).item(0)
  Assign row.item("Q2") = InputDataTable.rows(1).item(0)
  Assign row.item("Q3") = InputDataTable.rows(2).item(0)
  Assign row.item("Q4") = InputDataTable.rows(3).item(0)
  Clear datatable InputTable
Next row
Write range OutputTable to your excel file

The above assumes that the data will stay consistent for each ticker, but hopefully you get the point.

Thank you. I see what you’re getting at but I’m still messing it up still (I’m new to this). I read the table, and for each row grabs the ticker and input into Morningstar. I data scrape 4 numbers from a table on Morningstar and then write to range cell C2 on my table. I’m confused on how to get the assign functions to work. Does it automatically look in the first row for the Q1-Q4 headings?

Here’s a picture of my table before running it:

Here’s a picture after running it:

There are 27,000 of these tickers but the table format is always exactly the same.

This has some overlap but this is my sequence. In this case inputsTable is my excel file and ExtractDataTable is the info extracted from morningstar

I edited my post above because i forgot to include the .item(0) at the end.

I think what I put in still applies. If it isn’t making sense, would you mind uploading this .xaml and I can make the edits to show what I’m talking about? If not I can make a dummy file, but it’s faster/easier if you can upload :slight_smile:

Great thank you! Here it is. Since this is for 27,000 rows, I’d also be open to changing anything inefficient if you notice anything.

Main.xaml (23.6 KB)

I’m running 2018.1 so I can’t open your workflow :confused:

I’ll make a quick example showing you what I mean but it’ll take a bit as i have a meeting to run to in a few minutes

Do you have Orchestrator? If there are 27,000 rows this should be done using an orchestrator queue instead of an excel sheet. If you do need to go with the excel sheet, I would add a portion at the beginning to determine where in the excel sheet the work queue should begin (such as looking for the first null/empty/blank data in a column). If the robot errors out halfway through the process, you want to begin at the point it errors instead of redoing 13,500 rows again :slight_smile:

Oh wow, the older version doesn’t work already? I only downloaded it a month ago. Thanks for taking the time to help.

I actually just started going through the Orchestrator trainings. I have the demo “Tenant” account but I believe I can still use 1-2 bots on it. I’ll need to continue the training and figure out how.

My current workflow has it so that each time it grabs the table from the website it writes to the spreadsheet before continuing on. I figured if it failed part way through I would just cut the good ones into another sheet and therefore have the first failed row at row 2. I only need to do this whole process once.

Ok I went ahead and created a workflow with comments/annotations as needed. I skipped the entire part where you go to morninstar, find the ticker, and extract the table. Just put that part in where the comment is.

I also added a way to start where you left off in your excel file. This is done by using Datatable.Select to filter out only transactions with blank info in the Q4 column. This way it only updates those rows in the datatable, then once the whole process is completed, it writes everything back to the excel workbook. If an error occurs, no manual intervention would be needed, you just have to run the process again.

Please let me know if you have any questions on what it’s doing, or why and i’ll be happy to help!

Main.xaml (16.2 KB)

This is a big help. Though I’m running into an issue at “Grab Items not previously done” section. It can’t find column Q4. I’m not sure how this is supposed to work? Does it automatically know to look in row 1 to find the column headers? Or does it just look anywhere for Q4?

You need to check the box ‘AddHeaders’ within the properties of the Read Range activity

It is already checked but I fixed that issue by adding the full file path. However something strange is happening- It opens the excel sheet for a second and then closes it and the program ends. No error but nothing happens either.

Edit: The output says : “Error occurred at UiPath.System.Activities with the following message: Object reference not set to an instance of an object.
Please start the process again to complete processing of the file”

This is an error happening within the try-catch block. That error message(“Object reference not set to an instance of an object”) means that one of the variables you are using has no values assigned to it.

Try cutting the entire for each sequence from the try-catch and pasting it outside of it. Then disable the try-catch. Run it again and it will tell you exactly where the error is occurring.

The try-catch is normally something i’d put in at the very end after I’m done debugging :slight_smile:

Looks like its happening right when it hits the for each transaction right after reading the excel file. Any idea what this means?

object.Source: For each transaction
Message: Object reference not set to an instance of an
Exception Type: System.NullReferenceException
Location resultLocation)

I am not getting the same error when I try it. Can you verify that the variable types and scope are correct? TransactionsToComplete should be in the Main scope so it is accessible within the For Each loop. The type should be Array of DataRow

Try adding a write line directly after the assign activity TransactionsToComplete.Length.ToString to verify that items are being pulled into the array correctly

Also verify that the For Each property ‘TypeArgument’ is set to DataRow

Doesn’t look like it even makes it to the Assign because the write line doesn’t get run. It end within 1 second. I’m not sure if the excel file even fully opens.

all variables are in main scope

It looks like these are correct.
Transaction to Complete is System.Data.Datarow
For Each is System.Data.Datarow

I should have clarified better - do the write line activity after you assign TransactionsToComplete = inputsTable.Select(“Q4 is null OR Q4 = ‘’”)

I realized I accidently deleted the first Assign so the process works. There’s one last thing before this is working perfectly.

It puts the same data in Q1-Q4.

For example. The first number on the Morningstar list is 6.79. It places this number in all 4 columns.

Whoops, in my Assigns for the ‘Update Q2 data for current transaction’ it needs to be ExtractDataTable.Rows(1).Item(0)

Then Q3 needs to be Rows(2) and Q4 needs to be Rows(3).

I copy+pasted from Q1 assign and forgot to update :slight_smile:

1 Like

I actually was about to come back and edit my comment. I played around with it and found that as well. Thanks so much for your time and help!

1 Like