Convert Complex PDF Report to Excel
Budget: $30 – $250 USD
I am running MSExcel 365, and I receive a daily summary of my stock trading activity in a PDF file. Each "trade" is represented by 4 lines of data, each with multiple columns of data in each line that does not match the previous line. That is, each line has a unique layout. I used PowerQuery's PDF import feature, but PQ does not translate the fields consistently.
1. Create a PowerBI, or PowerQuery routine that will read in that day's PDF report, translate the data to Excel (steps outlined in the next steps), and save that Excel data in a temporary Data tab. I can look at the temporary tab for correctness, then provide me the ability to merge the contents of that temporary tab into the Master tab. Code this so I can perform this operation once each day for when I receive a PDF report.
2. Each new PDF report will be named with the date, so we cannot hard-code a specific name, but I need to be able to select the PDF file to be worked on.
3. I need all 4 rows (lines) of each trade translated consistently into one Excel row.
4. Do not worry about headers, or formatting, just get the data to consistently fall in the correct Excel columns. Do not worry about the dash-line separators in the PDF report, I just need the data. Again, the problem is to get the data to translate to the correct columns consistently from "trade" to "trade".
5. There are multiple pages in each PDF report. I need to be able to process all pages of the PDF report.
1. Create a PowerBI, or PowerQuery routine that will read in that day's PDF report, translate the data to Excel (steps outlined in the next steps), and save that Excel data in a temporary Data tab. I can look at the temporary tab for correctness, then provide me the ability to merge the contents of that temporary tab into the Master tab. Code this so I can perform this operation once each day for when I receive a PDF report.
2. Each new PDF report will be named with the date, so we cannot hard-code a specific name, but I need to be able to select the PDF file to be worked on.
3. I need all 4 rows (lines) of each trade translated consistently into one Excel row.
4. Do not worry about headers, or formatting, just get the data to consistently fall in the correct Excel columns. Do not worry about the dash-line separators in the PDF report, I just need the data. Again, the problem is to get the data to translate to the correct columns consistently from "trade" to "trade".
5. There are multiple pages in each PDF report. I need to be able to process all pages of the PDF report.