Import Data From a Website

[etm6477]

Hello Again Everyone! I hope you are all doing well since the last time I posted

I play Massachusetts state keno, and in the past I was able to copy the numbers off lottery's website, and paste them all into rows in Excel. Now the lottery has changed their website, so when I copy the numbers, they paste horizontally into columns instead of vertically into rows. I realize I can transpose each one individually, but that would take way too much effort, and time.

I believe what I am looking to do I guess is run a power query? Where the data is imported automatically into Excel from the lottery's website. I am looking to import all past results as well as all future results.

Attached is an example of the layout which includes the first 20 keno draws from today (6/5/23)

Also attached below are the links to the Massachusetts State Lottery keno past draws, as well as a link to Massachusetts State Lottery Data Base for today (I only know how to find the data bases by days, and not all the draws on one page)

https://www.masslottery.com/tools/pa...sort_by=oldest

https://www.masslottery.com/api/v1/k...ate=2023-06-05

I appreciate your help with this issue. Thank you all

[alansidman]

I have copied only the first 40 lines of data (2 games). If this is what you are looking for then with Power Query unpivot your data as shown in the attached.

Excel 2016 (Windows) 64 bit

	A	B	C	D
1	DATE	GAME #	Attribute	Value
2	6/5/2023	2576968	1	2
3	6/5/2023	2576968	2	8
4	6/5/2023	2576968	3	9
5	6/5/2023	2576968	4	13
6	6/5/2023	2576968	5	18
7	6/5/2023	2576968	6	23
8	6/5/2023	2576968	7	24
9	6/5/2023	2576968	8	27
10	6/5/2023	2576968	9	39
11	6/5/2023	2576968	10	47
12	6/5/2023	2576968	11	48
13	6/5/2023	2576968	12	53
14	6/5/2023	2576968	13	56
15	6/5/2023	2576968	14	58
16	6/5/2023	2576968	15	61
17	6/5/2023	2576968	16	66
18	6/5/2023	2576968	17	67
19	6/5/2023	2576968	18	68
20	6/5/2023	2576968	19	71
21	6/5/2023	2576968	20	80
22	6/5/2023	2576969	1	5
23	6/5/2023	2576969	2	22
24	6/5/2023	2576969	3	27
25	6/5/2023	2576969	4	28
26	6/5/2023	2576969	5	31
27	6/5/2023	2576969	6	32
28	6/5/2023	2576969	7	35
29	6/5/2023	2576969	8	37
30	6/5/2023	2576969	9	38
31	6/5/2023	2576969	10	39
32	6/5/2023	2576969	11	40
33	6/5/2023	2576969	12	43
34	6/5/2023	2576969	13	50
35	6/5/2023	2576969	14	62
36	6/5/2023	2576969	15	64
37	6/5/2023	2576969	16	67
38	6/5/2023	2576969	17	70
39	6/5/2023	2576969	18	74
40	6/5/2023	2576969	19	77
41	6/5/2023	2576969	20	79

Sheet: Table1

[etm6477]

Alan, I provided an excel sample of how I want it to look. I'll attach a PDF below of that excel page in case you couldn't see it
I'd like the 20 numbers from each game to go in each row, and not vertically in each column.
Is there a way that we can do that? Look at the sample & let me know. Thank you.

[alansidman]

I am very confused. The data in your spreadsheet and pdf look like the data on the website. I don't know what your issue is. Maybe you need to elaborate on your explanation.

[NewYears1978]

There's software that would really help with capturing tables on websites called "Table Capture" - It's a Chrome extension that I use for a similar thing.

However, you can use this macro as long as you are pasting the data in the same way I am, and it will transpose for you (and you can have as much pasted data in one sheet as you want, the only kicker is that I've set it up in a way to run the macro on a per date basis). So I pasted two pages of the data into a sheet and I renamed the sheet to the date "6-5-2023" then runt he macro "mTransposeMe" and it will put it all on a new tab.

Could be modified to do more and append to the same set of data but the way I did it is a one off you would then copy that or format it however. If you're doing this all the time it might be better to make it append to the existing sheet or whatever.

Please Login or Register  to view this content.

I am very confused. The data in your spreadsheet and pdf look like the data on the website. I don't know what your issue is. Maybe you need to elaborate on your explanation.

He wants it to look like the website, the issue is when copy and pasting from the website into Excel, it puts all the scores in rows and not horizontally.

[etm6477]

Hello Again Everyone! I hope you are all doing well since the last time I posted.

I play Massachusetts state keno, and in the past I was able to copy the numbers off the lottery's website, and paste them HORIZONTALLY (from left to right) into rows in Excel which from a function would insert them into their individual columns. Attached below is an example.
Now the lottery has changed their website, so when I copy the numbers that are horizontal on the website page, they paste vertically from top to bottom into columns on my excel spread sheet instead of horizontally into rows like I have in the Original Example.

I just found out about Query's, and from the little research I've done, I believe I am looking to run a power query where the data is imported automatically into Excel from the lottery's website. If you could help me, I am looking to import all past & future results.

Also attached is another example from the 1st 20 games from today 6/26/24.

So the question I am asking... How can I import the numbers automatically from the lottery's website (past results as well), so that they ship into my excel spreadsheat HORIZONTALLY from left to right? I want the information imported to the right side of the spread sheet beginning with the date.

Below are the links to the Massachusetts State Lottery keno past draws, as well as a link to Massachusetts State Lottery Data Base for today (I only know how to find the data bases by days, and not all the draws on one page)

https://www.masslottery.com/tools/pa...sort_by=oldest

https://www.masslottery.com/api/v1/k...ate=2024-06-26

I appreciate your help with this issue. Thank you all.